/*
 *                  BioJava development code
 *
 * This code may be freely distributed and modified under the
 * terms of the GNU Lesser General Public Licence.  This should
 * be distributed with the code.  If you do not have a copy,
 * see:
 *
 *      http://www.gnu.org/copyleft/lesser.html
 *
 * Copyright for this code is held jointly by the individual
 * authors.  These should be listed in @author doc comments.
 *
 * For more information on the BioJava project and its aims,
 * or to join the biojava-l mailing list, visit the home page
 * at:
 *
 *      http://www.biojava.org/
 * 
 * Created on May 23, 2005
 *
 */

package org.biojava.services.das.dao;

import java.net.URL;
import java.sql.*;
import java.util.ArrayList;
import java.util.EnumSet;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.logging.Logger;


import org.biojava.dasobert.das.Capabilities;
import org.biojava.dasobert.das.DasSpec;
import org.biojava.dasobert.dasregistry.Das1Source;
import org.biojava.dasobert.dasregistry.DasCoordinateSystem;
import org.biojava.dasobert.dasregistry.DasSource;
import org.biojava.dasobert.dasregistry.DasSourceFilter;
import org.biojava.services.das.registry.AutoActivation;
import org.biojava.services.das.registry.DasClient;
import org.biojava.services.das.registry.DasCoordinateSystemCollector;
import org.biojava.services.das.registry.DasRegistrySql;
import org.biojava.services.das.registry.RegistryException;
import org.biojava.services.das.registry.UserManager;
import org.biojava.services.das2.Das2Mirror;

import org.springframework.beans.factory.BeanFactory;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.validation.Errors;

import com.sun.org.apache.bcel.internal.generic.GETSTATIC;

/**
 * a class that takes care of dealing with the database it provides simple
 * tasks, each of them take a Connection as the first argument each method
 * throws SQLException
 */

public class DasSourceManager {
	private DasSourceManagerSql sqlUtil = new DasSourceManagerSql();

	public static Logger logger = Logger
			.getLogger("org.biojava.services.das.dao.DasSourceManager");
	private static int THREE_HOURS = 1000 * 60 * 60 * 3;
	// private static int ONE_MINUTE = 1000 * 60 ;

	public static final int UNKNOWN_AUTO_ID = -1;

	private static String BASIC_SELECT = "SELECT "
			+ " distinct(r.auto_id),r.url,r.adminemail,r.description,r.registerDate,"
			+ " r.leaseDate, r.helperurl, r.testcode, r.nickname, r.prefix, r.alert_admin, r.specification "
			+ " from registry r";

	DasSourceDAO sourceDAO;

	public DasSourceDAO getSourceDAO() {
		return sourceDAO;
	}

	public void setSourceDAO(DasSourceDAO sourceDAO) {
		this.sourceDAO = sourceDAO;
	}

	public DasSourceManager() {
		logger.finest("new dassource manager constructed");
	}

	private void doAutoIdSql(Connection conn, String sqlcmd, int auto_id)
			throws java.sql.SQLException {
		PreparedStatement ps = conn.prepareStatement(sqlcmd);
		ps.setInt(1, auto_id);
		ps.executeUpdate();
		ps.close();
	}

	/**
	 * get the auto_id for a DAS source if the URL is known
	 * 
	 * @param url
	 * 
	 * @return the auto_id of the das source with the url.
	 * @throws SQLException
	 */
	public int getAutoIdForURL(String url) {
		return sourceDAO.getAutoIdForURL(url);
		// String cmd = "select auto_id from registry where url = ?";
		// // System.out.println(cmd+url);
		// PreparedStatement ps = conn.prepareStatement(cmd);
		// ps.setString(1, url);
		// ResultSet set = ps.executeQuery();
		// int auto_id = UNKNOWN_AUTO_ID;
		// while (set.next()) {
		// auto_id = set.getInt(1);
		// }
		// set.close();
		// ps.close();
		// return auto_id;

	}

	/**
	 * get the Auto ID for a DAS source with a certain prefix. This is needed
	 * for DAS sources that are mirrors of others and where the prefix contains
	 * the URI assigned to this DAS source
	 * 
	 * @param conn
	 * @param prefix
	 * @return the auto_ID or UNKNOWN_AUTO_ID
	 * @throws SQLException
	 */
	public int getAutoIdForPrefix(Connection conn, String prefix)
			throws SQLException {
		String cmd = "select auto_id from registry where prefix = ?";
		PreparedStatement ps = conn.prepareStatement(cmd);
		ps.setString(1, prefix);
		ResultSet set = ps.executeQuery();
		int auto_id = UNKNOWN_AUTO_ID;
		while (set.next()) {
			auto_id = set.getInt(1);
		}
		set.close();
		ps.close();
		return auto_id;
	}

	public void pingLastModified(Connection conn) throws SQLException {
		String sql = "update admin set lastModified = now() where adid = 1";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.executeUpdate();

		ps.close();
	}

	public java.util.Date getLastModified(Connection conn) throws SQLException {
		String sql = "select lastModified from admin where adid = 1";
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet set = ps.executeQuery();
		java.util.Date date = new Date(0);

		while (set.next()) {
			Timestamp t = set.getTimestamp("lastModified");
			if (t != null) {

				date = new java.util.Date(t.getTime());
			}
		}
		set.close();
		ps.close();

		return date;

	}

	public DasClient getDasClient(Connection conn, int client_id)
			throws SQLException {

		DasClient client = null;

		String sql = "select name, iconpath, homepage, description "
				+ " from das_client " + " where client_id = ?";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, client_id);
		ResultSet set = ps.executeQuery();
		while (set.next()) {
			String name = set.getString(1);
			String path = set.getString(2);
			String homepage = set.getString(3);
			String desc = set.getString(4);
			client = new DasClient();
			try {
				client.setHomepage(new URL(homepage));
			} catch (Exception e) {
				e.printStackTrace();
			}
			client.setName(name);
			client.setIconPath(path);
			client.setDescription(desc);
		}
		set.close();
		ps.close();

		return client;
	}

	public void removeAutoActivation(Connection conn, int auto_id,
			String activationUrl) throws SQLException {
		// System.out.println("removin auto activation " + auto_id + " "
		// + activationUrl);

		String sql = "delete from auto_activation where auto_id = ? and activation =?";
		PreparedStatement ps = conn.prepareStatement(sql);

		ps.setInt(1, auto_id);
		ps.setString(2, activationUrl);
		ps.executeUpdate();

		ps.close();
	}

	public int createAutoActivation(Connection conn, AutoActivation aa,
			int coordNS_auto, String ds_url) throws SQLException {

		// System.out.println("DasSourceManager create AutoActivation "
		// + aa.getActivationURL() + " " + coordNS_auto + " " + ds_url);

		// step one: get client_id;
		String client_sql = "select client_id from das_client where name = ?";

		PreparedStatement client_prep = conn.prepareStatement(client_sql);

		client_prep.setString(1, aa.getClient().getName());

		int client_id = -1;

		ResultSet client_row = client_prep.executeQuery();
		while (client_row.next()) {
			client_id = client_row.getInt(1);
		}
		client_row.close();
		client_prep.close();

		// step two: auto_id
		int auto_id = getAutoIdForURL(ds_url);

		String sql = "insert into auto_activation (client_id, coordNS_auto, activation, auto_id) "
				+ " values 								  (?        , ?           , ?         , ?      );";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, client_id);
		ps.setInt(2, coordNS_auto);
		ps.setString(3, aa.getActivationURL());
		ps.setInt(4, auto_id);

		ps.executeUpdate();
		int id = getLastInsertId(ps);

		ps.close();

		return id;

	}

	/**
	 * get the AutoActivation information that is stored for a particular
	 * DasSource
	 * 
	 * @param conn
	 *            database connection
	 * @param ds
	 *            the das source
	 * @param auto_id
	 *            database primary accession code for the DAS source
	 * @return the AutoActivation URLs for a DAS source
	 * @throws SQLException
	 */
	public AutoActivation[] getAutoActivation(Connection conn, DasSource ds,
			int auto_id) throws SQLException {

		List<AutoActivation> data = new ArrayList<AutoActivation>();
		// System.out.println(" **** getAutoActivation " + auto_id);
		String sql = "select client_id, coordNS_auto, activation from auto_activation where auto_id = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, auto_id);
		ResultSet set = ps.executeQuery();
		while (set.next()) {

			int client_id = set.getInt(1);
			int coordNS_auto = set.getInt(2);
			String activation = set.getString(3);

			DasClient client = getDasClient(conn, client_id);

			AutoActivation aa = new AutoActivation();
			aa.setActivationURL(activation);
			aa.setClient(client);
			// System.out.println(" " + coordNS_auto);
			DasCoordinateSystem coordSys = getCoordinateSystem(conn,
					coordNS_auto);
			// System.out.println("got coordsys " +coordSys);
			// hack.. :-( todo: find a nicer way for this...
			for (int i = 0; i < ds.getCoordinateSystem().length; i++) {
				DasCoordinateSystem tmp = ds.getCoordinateSystem()[i];
				if (tmp.toString().equals(coordSys.toString())) {
					// sSystem.out.println("*** matching " +
					// coordSys.getTestCode() + " " + tmp.getTestCode());
					coordSys.setTestCode(tmp.getTestCode());
				}
			}

			aa.setCoordSys(coordSys);
			data.add(aa);

		}

		set.close();
		ps.close();

		return (AutoActivation[]) data.toArray(new AutoActivation[data.size()]);

	}

	/**
	 * check if new DAS service is already in Database
	 * 
	 * @param conn
	 * @param url
	 * @return a flag if a server with a certain URL is in the database
	 */
	public boolean isInDatabase(Connection conn, String url) {
		int auto_id = UNKNOWN_AUTO_ID;

		auto_id = getAutoIdForURL(url);

		if (auto_id == UNKNOWN_AUTO_ID)
			return false;

		return true;

	}

	/**
	 * remove a DAS source that has been registered in this installation (and is
	 * not cached from somehwere else)
	 * 
	 * 
	 * @param conn
	 * @param auto_id
	 * @throws SQLException
	 */
	public void removeLocalSource(int auto_id)
			throws SQLException {
		// only can remove local DAS sources ...
		// local das sources have prefix = null, public ones have prefix !=
		// null;
		// had to change the above null clause as we will never be able to get
		// rid of sources that are not local as the mirroring does not delete if
		// sources no longer exist
		// String cmd =
		// "delete from registry where auto_id =? and prefix IS NULL";

		// lets now archive all deleted servers
		sourceDAO.setActiveSource(auto_id, false);

	}

	/**
	 * remove a cached das source
	 * 
	 * @param conn
	 * @param auto_id
	 * @throws SQLException
	 * @throws RegistryException
	 *             if the DAS source is not a local DAS source
	 * */
	public void removeCachedSource(Connection conn, int auto_id)
			throws SQLException, RegistryException {

		// only can remove cached DAS sources ...
		// local das sources have prefix = null, public ones have prefix !=
		// null;

		String cmd1 = "select nickname from registry where auto_id = ? and prefix is NOT NULL";
		PreparedStatement ps = conn.prepareStatement(cmd1);
		ps.setInt(1, auto_id);
		ResultSet set = ps.executeQuery();
		boolean canRemove = false;
		while (set.next()) {
			String nickname = set.getString(1);
			// System.out.println(nickname);
			canRemove = true;
			break;
		}

		if (!canRemove) {
			String msg = "can not remove das source with auto_id " + auto_id
					+ " - this is a local DAS source (prefix is NOT NULL)";
			throw new RegistryException(msg);
		}

		String cmd = "delete from registry where auto_id =? and prefix IS NOT NULL";
		// System.out.println(cmd);
		doAutoIdSql(conn, cmd, auto_id);

		removedCachedSecondaryData(conn, auto_id);

		String cmd5 = "delete from registry2category where auto_id=?";
		doAutoIdSql(conn, cmd5, auto_id);

		resetRegistryAutoIncrement(conn);
	}

	private void resetRegistryAutoIncrement(Connection conn)
			throws SQLException {
		// THIS WILL ONLY WORK WITH Mysql MyIsam tables!!!!
		// reset the auto-counter
		String cmd6 = "alter table registry AUTO_INCREMENT =1 ";
		PreparedStatement ps = conn.prepareStatement(cmd6);
		ps.executeUpdate();
		ps.close();
	}

	private void resetCoordNSAutoIncrement(Connection conn) throws SQLException {
		// THIS WILL ONLY WORK WITH Mysql MyIsam tables!!!!
		// reset auto increment
		String cmd2 = "alter table coordNS AUTO_INCREMENT = 1";
		PreparedStatement ps2 = conn.prepareStatement(cmd2);
		ps2.executeUpdate();
		ps2.close();
	}

	/**
	 * renew the lease for a server
	 * 
	 * @param conn
	 * @param serverurl
	 *            the url of the DAS server
	 * @throws SQLException
	 */
	public void renewLeaseNow(Connection conn, String serverurl)
			throws SQLException {
		String cmd = "UPDATE registry set leaseDate=now(), alert_sent=0 where url = ?";
		PreparedStatement ps = conn.prepareStatement(cmd);
		ps.setString(1, serverurl);

		ps.executeUpdate();
		ps.close();

	}

	public List<DasSource> findService(Connection conn, String capability,
			String[] categories, String[] names, String[] organisms,
			String[] labels, String prefix, String spec) throws SQLException {

		List<DasSource> lst = new ArrayList<DasSource>();

		// build up a monster SQL query to retreive all matching DasSource

		// String sql =
		// "SELECT DISTINCT(registry.auto_id),registry.url,registry.adminemail,"
		// +
		// "   registry.description,registry.registerDate,registry.leaseDate,helperurl,"
		// + "testcode,nickname,registry.prefix,alert_admin "
		// +
		// // ",cs_names.coordinateSystem, " +
		// // "   category.category, organism.preferred_name " +
		// " from "
		// + "   registry2coordNS, registry,coordNS,registry2label "
		// + " where registry.auto_id = registry2coordNS.auto_id and "
		// + "   registry2coordNS.coordNS_auto= coordNS.coordNS_auto";
		// String sql=sqlUtil.findServiceSql();
		String sql = "SELECT DISTINCT(registry.auto_id),registry.url,registry.adminemail,"
				+ "   registry.description,registry.registerDate,registry.leaseDate,helperurl,"
				+ "testcode,nickname,registry.prefix,alert_admin, specification "
				+ " from ";
		// "   coordNS.coord_auto = cs_names.coord_auto and " +
		// "   coordNS.category_auto = category.category_auto " ;

		// check capability
		boolean found = Capabilities.exists(capability);
		// System.out.println("checking capability="+capability);
		if (found)
			sql += " validated_capabilities, ";

		sql += "   registry2coordNS, registry,coordNS,registry2label"
				+ " where registry.auto_id = registry2coordNS.auto_id and "
				+ "   registry2coordNS.coordNS_auto= coordNS.coordNS_auto";

		if (found) {

			sql += " and validated_capabilities.auto_id =registry.auto_id and validated_capabilities."
					+ Capabilities.getValue(capability) + " =1 ";
		}

		// find the category ids for the given categories ...
		// this is also a security check, so only valid text is used in the long
		// SQL statement created below
		Integer[] category_autos = getCategoryAutos(conn, categories);
		Integer[] coord_autos = getNameAutos(conn, names);
		Integer[] tax_ids = getTaxIds(conn, organisms);
		Integer[] label_autos = getLabelAutos(conn, labels);

		// System.out.println("checkpoint " + category_autos.length + " " +
		// coord_autos.length + " " + tax_ids.length+ " " + label_autos.length);

		// now add name (coordiante systems)
		for (int c = 0; c < coord_autos.length; c++) {
			if (c == 0)
				sql += " and ( ";
			if (c > 0)
				sql += " or ";
			sql += " (coordNS.coord_auto = " + coord_autos[c] + " ) ";

		}
		if (coord_autos.length > 0) {
			sql += " ) ";
		}

		// now add categories;
		for (int c = 0; c < category_autos.length; c++) {
			if (c == 0)
				sql += " and ( ";
			if (c > 0)
				sql += " or ";
			sql += " ( coordNS.category_auto= " + category_autos[c] + ") ";
		}
		if (category_autos.length > 0) {
			sql += " ) ";
		}

		// and organism
		for (int o = 0; o < tax_ids.length; o++) {
			if (o == 0)
				sql += " and ( ";
			if (o > 0)
				sql += " or ";
			sql += " ( coordNS.ncbi_tax_id = " + tax_ids[o] + ") ";
		}
		if (tax_ids.length > 0) {
			sql += " ) ";
		}

		// and labels
		if (label_autos.length > 0) {
			sql += " and registry2label.auto_id = registry.auto_id ";
		}
		for (int o = 0; o < label_autos.length; o++) {
			if (o == 0)
				sql += " and ( ";
			if (o > 0)
				sql += " or ";
			sql += " ( registry2label.label_auto = " + label_autos[o] + ") ";
		}
		if (label_autos.length > 0) {
			sql += " ) ";
		}
		// SELECT DISTINCT(registry.auto_id),registry.url,registry.adminemail,
		// registry.description,registry.registerDate,registry.leaseDate,helperurl,testcode,nickname,registry.prefix,alert_admin,
		// specification from registry2coordNS, registry,coordNS,registry2label
		// where registry.auto_id = registry2coordNS.auto_id and
		// registry2coordNS.coordNS_auto= coordNS.coordNS_auto and
		// specification='DAS/1.6E';
		if (spec.equals("any") || spec.equals("") || spec.equals(null)) {

		} else {
			DasSpec dasSpec = DasSpec.convertToRegistrySpecification(spec);
			System.out.println("converted spec=" + dasSpec.toString());
			sql += " and specification='" + dasSpec.toString() + "'";
		}
		DasCoordinateSystemCollector coordmap = getAllCoordinateSystemsMap(
				conn, prefix);
		// System.out.println(sql);
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet row = ps.executeQuery();

		while (row.next()) {
			// build up DasRegistry object from row
			DasSource ds = getDasSourceFromRow(conn, row, coordmap, prefix);
			lst.add(ds);
		}
		row.close();
		ps.close();

		return lst;
	}

	/**
	 * remove a cached coordiante system
	 * 
	 * @param conn
	 * @param coordNS_auto
	 * @throws SQLException
	 */
	public void removeCachedCoordSys(Connection conn, int coordNS_auto)
			throws SQLException {
		// only can remove cached coord sys.
		// local coordsys have prefix = null, public ones have prefix != null
		String cmd = "delete from coordNS where coordNS_auto = ? and prefix IS NOT NULL";
		// System.out.println(cmd);

		PreparedStatement ps = conn.prepareStatement(cmd);
		ps.setInt(1, coordNS_auto);
		ps.executeUpdate();
		ps.close();

		resetCoordNSAutoIncrement(conn);
	}

	/**
	 * checks if a nickname is already used by any of the DAS sources
	 * 
	 * @param conn
	 * @param nickname
	 * @return a flag indicating if nickname is used or not
	 * @throws SQLException
	 */
	public boolean isKnownNickname(Connection conn, String nickname)
			throws SQLException {
		String sql = "SELECT * from registry where nickname = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, nickname);
		ResultSet rs = ps.executeQuery();

		while (rs.next()) {
			return true;
		}
		rs.close();
		ps.close();
		return false;
	}

	public boolean isKnownPrefix(Connection conn, String prefix) {

		logger.fine("in isKnownPrefix method, prefix=" + prefix);

		try {
			String sql = "SELECT * from registry where prefix = ?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, prefix);
			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				logger.fine("prefix found in registry database");
				return true;
			}
			rs.close();
			ps.close();
		} catch (Exception e) {
			e.printStackTrace();

		}
		logger.fine("prefix not found in registry database");
		return false;
	}

	/**
	 * check if a coordinate system is already stored in the db
	 * 
	 * @param conn
	 *            db connection
	 * @param cs
	 *            the coord sys to check
	 * @return a flag if the das source is known
	 * @throws SQLException
	 */
	public boolean isKnownCoordinateSystem(Connection conn,
			DasCoordinateSystem cs) throws SQLException {

		boolean isKnown = false;

		int coord_auto = getCoordAuto(conn, cs.getName(), cs.getVersion());
		if (coord_auto < 0)
			return false;

		int cat = getCategoryAuto(conn, cs.getCategory());
		if (cat < 0)
			return false;

		// now check the coordNS table
		int taxid = cs.getNCBITaxId();

		String sql = "select * from coordNS where category_auto = ? and coord_auto = ? and ncbi_tax_id = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, cat);
		ps.setInt(2, coord_auto);
		ps.setInt(3, taxid);

		ResultSet set = ps.executeQuery();
		if (set.next()) {
			isKnown = true;
		}

		set.close();
		ps.close();
		if (isKnown)
			logger.info("coordinate system is already known to the registry");
		return isKnown;

	}

	/**
	 * create a new coordinate system
	 * 
	 * @param conn
	 * @param coord_auto
	 * @param category_auto
	 * @param ncbi_tax_id
	 * @param prefix
	 * @return the auto-increment id produced by mysql
	 * @throws SQLException
	 */
	public int createCoordSys(Connection conn, int coord_auto,
			int category_auto, int ncbi_tax_id, String prefix)
			throws SQLException {

		logger.info("creating new coord sys " + coord_auto + " "
				+ category_auto + " " + ncbi_tax_id);

		String sqlNS = "insert into coordNS ( coord_auto, category_auto, ncbi_tax_id ,prefix)  values (?,?,?,?)";
		PreparedStatement ps = conn.prepareStatement(sqlNS);

		ps.setInt(1, coord_auto);
		ps.setInt(2, category_auto);
		ps.setInt(3, ncbi_tax_id);
		ps.setString(4, prefix);
		ps.executeUpdate();
		int id = getLastInsertId(ps);
		ps.close();
		return id;

	}

	/**
	 * rename the category_auto of the category or -1 if not know...
	 * 
	 * @param conn
	 * @param category
	 * @return the category_auto of the category or -1 if not found
	 * @throws SQLException
	 */
	public int getCategoryAuto(Connection conn, String category)
			throws SQLException {
		logger.fine("getCategoryAuto category=" + category);
		String sql = "select category_auto from category where category = ?";
		PreparedStatement ps = conn.prepareStatement(sql);

		ps.setString(1, category);

		ResultSet rs = ps.executeQuery();

		int cat = -1;
		if (rs.next()) {
			cat = rs.getInt(1);
		}

		rs.close();
		ps.close();
		return cat;

	}

	/**
	 * get the coord_auto from cs_names table
	 * 
	 * @param conn
	 * @param name
	 * @param version
	 * @return the coord_auto field for the coordsys name
	 * @throws SQLException
	 */

	public int getCoordAuto(Connection conn, String name, String version)
			throws SQLException {

		String sql;
		if (version != null) {
			sql = "SELECT coord_auto from cs_names where coordinateSystem = ? and version = ?";
		} else {
			sql = "SELECT coord_auto from cs_names where coordinateSystem = ? ";
		}

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, name);
		if (version != null)
			ps.setString(2, version);

		ResultSet rs = ps.executeQuery();
		int id = -1;
		if (rs.next()) {
			id = rs.getInt(1);
		}
		rs.close();
		ps.close();
		return id;
	}

	public String[] getAllLabels(Connection conn) throws SQLException {

		List<String> labels = new ArrayList<String>();

		String sql = "SELECT label from label";

		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			labels.add(rs.getString(1));
		}

		rs.close();
		ps.close();
		return (String[]) labels.toArray(new String[labels.size()]);
	}

	private String[] getLabels(Connection conn, int auto_id)
			throws SQLException {
		String sql = "select label.label from registry2label,label where registry2label.auto_id = ? "
				+ " and registry2label.label_auto = label.label_auto";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, auto_id);
		ResultSet row = ps.executeQuery();
		List<String> lst = new ArrayList<String>();
		while (row.next()) {
			lst.add(row.getString(1));
		}
		row.close();
		ps.close();
		return (String[]) lst.toArray(new String[lst.size()]);

	}

	/*
	 * retrieve all coordinate systems that are provided by service auto_id
	 */
	private DasCoordinateSystem[] getCoordinateSystemsForDasSource(
			Connection conn, int auto_id, DasCoordinateSystemCollector coordmap)
			throws SQLException {
		// System.out.println("getCoordinateSystems " + auto_id);
		List<DasCoordinateSystem> lst = new ArrayList<DasCoordinateSystem>();

		// todo change to new style ..
		String sql = "select  coordNS_auto, testregion "
				+ " from registry2coordNS where registry2coordNS.auto_id = ?";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, auto_id);

		ResultSet row = ps.executeQuery();

		while (row.next()) {

			int coordNS_auto = row.getInt(1);

			String testregion = row.getString(2);

			DasCoordinateSystem csname = coordmap
					.get(new Integer(coordNS_auto));
			DasCoordinateSystem cs = (DasCoordinateSystem) csname.clone();

			cs.setTestCode(testregion);
			lst.add(cs);

		}
		row.close();
		ps.close();

		return (DasCoordinateSystem[]) lst.toArray(new DasCoordinateSystem[lst
				.size()]);

	}

	/*
	 * returns the capabilities of service auto_id
	 */
	private List<String> get_capabilities(Connection conn, int auto_id) {
		// System.out.println("get_capabilities");
		try {
			List<String> capabilities = new ArrayList<String>();

			String sql = sqlUtil.getStatedCapabiltiesSql();

			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, auto_id);

			ResultSet row = ps.executeQuery();

			// only one result expected ...
			boolean ok = row.next();
			if (!ok) {
				// logger.infoing("did not find a result set for auto_id " +
				// auto_id);
			}

			/* test which capabilities are supported by this service */
			for (Capabilities cap : Capabilities.values()) {

				if (hasCapability(row, cap.getCommand())) {
					capabilities.add(cap.getName());
					//System.out.println("found capability:"+cap.getName());
				}

			}

			// System.out.println("cap size"+capabilities.size());
			if (capabilities.size() == 0)
				return null;
			

			row.close();
			ps.close();
			// System.out.println(str.length);
			return capabilities;

		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/* helper function for get_capabilities */
	private boolean hasCapability(ResultSet row, String capability) {
		try {
			int cap = row.getInt(capability);

			if (cap > 0) {
				return true;
			} else {
				return false;
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;

	}

	/**
	 * security check, since we build up an SQL statement using free user input,
	 * we need to restrict the input to avoid SQL infusion
	 */
	private String protectDatabaseText(String keyword) {

		if (keyword.length() > 100) {
			keyword = keyword.substring(0, 99);
		}
		keyword = keyword.toLowerCase();
		keyword = keyword.replaceAll("%", "");
		keyword = keyword.replaceAll("insert", "");
		keyword = keyword.replaceAll("select", "");
		keyword = keyword.replaceAll("update", "");
		keyword = keyword.replaceAll("\\\\", "");
		keyword = keyword.replaceAll("\\(", "");
		keyword = keyword.replaceAll("\\)", "");
		keyword = keyword.replaceAll(";", "");

		return keyword;
	}

	public DasSource[] getCachedSources(Connection conn, String prefix)
			throws SQLException {

		String SELECT_CACHED_SQL = "SELECT auto_id, url FROM registry where prefix like ?";

		PreparedStatement ps = conn.prepareStatement(SELECT_CACHED_SQL);
		ps.setString(1, prefix);
		ResultSet rs = ps.executeQuery();

		List<DasSource> sources = new ArrayList<DasSource>();
		while (rs.next()) {
			// int auto_id = rs.getInt("auto_id");
			String url = rs.getString("url");
			DasSource ds = getDasSourceByUrl(conn, url, prefix);
			sources.add(ds);
		}

		return (DasSource[]) sources.toArray(new DasSource[sources.size()]);

	}

	public List<DasSource> getAllSources(Connection conn, String prefix)
			throws SQLException {
		System.out.println("prefix=" + prefix);
		DasCoordinateSystemCollector coordmap = getAllCoordinateSystemsMap(
				conn, prefix);

		String cmd2 = BASIC_SELECT + " where protocol != 2 and active=1";
		// String cmd2 = sqlUtil.getAllSourcesSql();
		PreparedStatement ps = conn.prepareStatement(cmd2);
		logger.fine("sql for all sources: " + cmd2);
		// System.out.println(cmd2);
		ResultSet row = ps.executeQuery();

		List<DasSource> sources = new ArrayList<DasSource>();
		// int i = 0;
		while (row.next()) {
			// i ++;
			// DasSource ds = getDasSourceFromRow(conn, row, coordmap, prefix);
			DasSource ds = getDasSourceFromRow(conn, row, coordmap, prefix);
			// System.out.println(ds);
			// System.out.println("setting " + i + "adding: " + knowSourcesSize
			// + ds.getUrl());
			// System.out.println("i="+i);
			sources.add(ds);
		}

		// System.out.println("returning"+sources[0] );
		row.close();
		ps.close();

		return sources;

	}

	public List<DasSource> getAllSourcesForServer(Connection conn,
			String prefix, int serverId) throws SQLException {
		DasCoordinateSystemCollector coordmap = getAllCoordinateSystemsMap(
				conn, prefix);

		String cmd2 = BASIC_SELECT
				+ " where protocol != 2 and server_id=? and active=1";
		// String cmd2 = sqlUtil.getAllSourcesSql();
		PreparedStatement ps = conn.prepareStatement(cmd2);
		ps.setInt(1, serverId);
		logger.fine("sql for all sources: " + cmd2);
		// System.out.println(cmd2);
		ResultSet row = ps.executeQuery();

		List<DasSource> sources = new ArrayList<DasSource>();
		// int i = 0;
		while (row.next()) {
			// i ++;
			// DasSource ds = getDasSourceFromRow(conn, row, coordmap, prefix);
			DasSource ds = getDasSourceFromRow(conn, row, coordmap, prefix);
			// System.out.println(ds);
			// System.out.println("setting " + i + "adding: " + knowSourcesSize
			// + ds.getUrl());
			// System.out.println("i="+i);
			sources.add(ds);
		}

		// System.out.println("returning"+sources[0] );
		row.close();
		ps.close();

		return sources;

	}

	/**
	 * perform a keywords search against the data base
	 * 
	 * @param conn
	 * @param keyword
	 * @param prefix
	 *            thel ocal prefix of this registry - used for creating unique
	 *            IDs.
	 * @return all DasSources that match this keyword
	 * @throws SQLException
	 */
	public List<DasSource> keywordSearch(Connection conn, String keyword,
			String prefix) throws SQLException {

		keyword = protectDatabaseText(keyword);

		String KEYWORDSEARCHSQL = BASIC_SELECT
				+ " , label,registry2label "
				+ " where ( protocol != 2) and "
				+ " ( r.auto_id like '%"
				+ keyword
				+ "%') or ( r.nickname like '%"
				+ keyword
				+ "%' ) or "
				+ " ( r.url like '%"
				+ keyword
				+ "%' ) or "
				+ "       ( r.description like '%"
				+ keyword
				+ "%' ) or "
				+ "       (r.adminemail like '%"
				+ keyword
				+ "%') or (r.helperurl like '%"
				+ keyword
				+ "%' ) or "
				+ "       ( r.testcode like '%"
				+ keyword
				+ "%')  or "
				+ "       ( ( label.label like '%"
				+ keyword
				+ "%') and "
				+ " label.label_auto = registry2label.label_auto and registry2label.auto_id = r.auto_id )";

		System.out.println(KEYWORDSEARCHSQL + " " + keyword);

		PreparedStatement sql = conn.prepareStatement(KEYWORDSEARCHSQL);

		ResultSet row = sql.executeQuery();
		List<DasSource> dss = new ArrayList<DasSource>();
		DasCoordinateSystemCollector coordmap = getAllCoordinateSystemsMap(
				conn, prefix);
		while (row.next()) {
			DasSource ds = getDasSourceFromRow(conn, row, coordmap, prefix);
			dss.add(ds);
		}
		row.close();
		sql.close();

		return dss;

	}

	public List<DasSource> typesSearch(Connection conn, String keyword,
			String prefix) throws SQLException {
		keyword = protectDatabaseText(keyword);

		String searchSql = BASIC_SELECT
				+ " ,types where ( protocol != 2)  and  ((types.id like '%"
				+ keyword + "%') or (types.category like '%" + keyword
				+ "%') or (types.cvId like '%" + keyword
				+ "%')) and r.auto_id=types.auto_id";

		System.out.println(searchSql + " " + keyword);

		PreparedStatement sql = conn.prepareStatement(searchSql);

		ResultSet row = sql.executeQuery();
		List<DasSource> dss = new ArrayList<DasSource>();
		DasCoordinateSystemCollector coordmap = getAllCoordinateSystemsMap(
				conn, prefix);
		while (row.next()) {
			DasSource ds = getDasSourceFromRow(conn, row, coordmap, prefix);
			dss.add(ds);
		}
		row.close();
		sql.close();

		return dss;

	}

	/**
	 * convert row to DasSource for "SELECT
	 * auto_id,url,adminemail,description,registerDate,leaseDate from registry"
	 * sql queries
	 * 
	 * @param conn
	 * @param row
	 * @param coordmap
	 * @param prefix
	 *            - the prefix to put into the unique ID of the DAs source
	 * @return a DasSource object
	 * @throws SQLException
	 */
	public DasSource getDasSourceFromRow(Connection conn, ResultSet row,
			DasCoordinateSystemCollector coordmap, String prefix)
			throws SQLException {
		// System.out.println("getDasSourceFromRow");
		int auto_id = row.getInt(1);
		String url = row.getString(2);
		String adminemail = row.getString(3);
		String description = row.getString(4);
		Timestamp regTime = row.getTimestamp(5);
		Timestamp leaseTime = row.getTimestamp(6);
		String helperurl = row.getString(7);
		String nickname = row.getString(9);
		String thisprefix = row.getString(10);
		String alertMe = row.getString(11);
		String specification = row.getString("specification");

		// System.out.println("DasSourceManager: converting the dates");
		java.util.Date registerDate = new Date(regTime.getTime());
		java.util.Date leaseDate = new Date(leaseTime.getTime());

		List<String> capabilities = get_capabilities(conn, auto_id);
		List<String> validCapabilities = getValidCapabilities(conn, auto_id);
		Map<Capabilities, Long> times = sourceDAO.getCapabilityTimes(auto_id);
		DasCoordinateSystem[] cs_names = getCoordinateSystemsForDasSource(conn,
				auto_id, coordmap);

		String[] labels = getLabels(conn, auto_id);

		DasSource ds = new Das1Source();

		String idstring = prefix + DasRegistrySql.PREFIXSPLIT + auto_id;
		// System.out.println(" in getSourceFromRow="+idstring);
		if (thisprefix == null) {
			ds.setLocal(true);
		} else {
			ds.setLocal(false);
			idstring = thisprefix;
		}

		ds.setId(idstring);
		ds.setUrl(url);
		ds.setAdminemail(adminemail);
		ds.setDescription(description);
		ds.setCoordinateSystem(cs_names);
		ds.setCapabilities(capabilities);
		if(times!=null){
			ds.setCapabilityTime(times);		
		}
	
		ds.setValidCapabilities(validCapabilities);
		ds.setRegisterDate(registerDate);
		ds.setLeaseDate(leaseDate);
		ds.setHelperurl(helperurl);
		if (specification != null) {
			ds.setSpecification(specification);
		} else {
			ds.setSpecification("Not specified by server");
		}
		// now part of coordinate system
		// ds.setTestCode(testcode);
		ds.setLabels(labels);
		ds.setNickname(nickname);
		if (alertMe.equals("1"))
			ds.setAlertAdmin(true);
		else {
			ds.setAlertAdmin(false);
		}

		// System.out.println("without capabilities: "+ds);
		return ds;

	}

	/**
	 * convert row to DasSource for "SELECT
	 * auto_id,url,adminemail,description,registerDate,leaseDate from registry"
	 * sql queries with stated capabilities and validated capabilities added
	 * 
	 * @param conn
	 * @param row
	 * @param coordmap
	 * @param prefix
	 *            - the prefix to put into the unique ID of the DAs source
	 * @return a DasSource object
	 * @throws SQLException
	 */
	// public DasSource getDasSourceFromRowWithCapabilities(Connection conn,
	// ResultSet row, DasCoordinateSystemCollector coordmap, String prefix)
	// throws SQLException {
	//
	// ArrayList statedCapabilities = new ArrayList<String>();
	// ArrayList validCapabilities = new ArrayList();
	// int auto_id = row.getInt(1);
	// String url = row.getString(2);
	// String adminemail = row.getString(3);
	// String description = row.getString(4);
	// Timestamp regTime = row.getTimestamp(5);
	// Timestamp leaseTime = row.getTimestamp(6);
	// String helperurl = row.getString(7);
	// String nickname = row.getString(9);
	// String thisprefix = row.getString(10);
	// String alertMe = row.getString(11);
	//
	// // System.out.println("DasSourceManager: converting the dates");
	// java.util.Date registerDate = new Date(regTime.getTime());
	// java.util.Date leaseDate = new Date(leaseTime.getTime());
	//
	// // String[] capabilities = get_capabilities(conn, auto_id);
	// /* test which capabilities are supported by this service */
	//
	// for (Capabilities cap : EnumSet.allOf(Capabilities.class)) {
	// // need to use enumallof here instead of values for efficiency
	//
	// if (hasCapability(row, cap.toString())) {
	// statedCapabilities.add((String) cap.toString());
	// }
	// if (hasCapability(row, "v" + cap.toString())) {
	// validCapabilities.add((String) cap.toString());
	// }
	//
	// }
	//
	// // String[] validCapabilities = getValidCapabilities(conn, auto_id);
	//
	// DasCoordinateSystem[] cs_names = getCoordinateSystemsForDasSource(conn,
	// auto_id, coordmap);
	//
	// String[] labels = getLabels(conn, auto_id);
	//
	// DasSource ds = new Das1Source();
	//
	// String idstring = prefix + DasRegistrySql.PREFIXSPLIT + auto_id;
	// if (thisprefix == null) {
	// ds.setLocal(true);
	// } else {
	// ds.setLocal(false);
	// idstring = thisprefix;
	// }
	//
	// ds.setId(idstring);
	// ds.setUrl(url);
	// ds.setAdminemail(adminemail);
	// ds.setDescription(description);
	// ds.setCoordinateSystem(cs_names);
	// ds.setCapabilities((String[]) (statedCapabilities
	// .toArray(new String[statedCapabilities.size()])));
	// ds.setValidCapabilities((String[]) validCapabilities
	// .toArray(new String[validCapabilities.size()]));
	// ds.setRegisterDate(registerDate);
	// ds.setLeaseDate(leaseDate);
	// ds.setHelperurl(helperurl);
	// // now part of coordinate system
	// // ds.setTestCode(testcode);
	// ds.setLabels(labels);
	// ds.setNickname(nickname);
	// if (alertMe.equals("1"))
	// ds.setAlertAdmin(true);
	// else {
	// ds.setAlertAdmin(false);
	// }
	//
	// // System.out.println(ds);
	// return ds;
	//
	// }

	private boolean hasLocalPrefix(String id, String prefix) {

		String thisPrefix = "";
		try {
			thisPrefix = DasRegistrySql.getPrefix(id);

		} catch (RegistryException rex) {
			rex.printStackTrace();
			return false;
		}

		if (thisPrefix.equals(prefix))
			return true;
		return false;
	}

	public DasSource getDasSourceById(Connection conn, int auto_id)
			throws SQLException {
		DasSource ds = new Das1Source();

		String sql = BASIC_SELECT + " where auto_id = ?";
		PreparedStatement ps = conn.prepareStatement(sql);

		ps.setInt(1, auto_id);
		// TODO: get this from config for Mirroring!
		String prefix = null;

		ResultSet row = ps.executeQuery();

		if (row.next()) {

			DasCoordinateSystemCollector coordmap = getAllCoordinateSystemsMap(
					conn, prefix);
			ds = getDasSourceFromRow(conn, row, coordmap, prefix);
			DasCoordinateSystem[] dcss = getCoordinateSystemsForDasSource(conn,
					auto_id, coordmap);
			ds.setCoordinateSystem(dcss);

		}
		row.close();
		ps.close();

		return ds;
	}

	private DasSource getDasSourceById(Connection conn, int auto_id,
			DasCoordinateSystemCollector coordmap, String prefix)
			throws SQLException {
		DasSource ds = new Das1Source();

		String sql = BASIC_SELECT + " where r.auto_id = ?";
		PreparedStatement ps = conn.prepareStatement(sql);

		ps.setInt(1, auto_id);

		ResultSet row = ps.executeQuery();

		if (row.next()) {
			ds = getDasSourceFromRow(conn, row, coordmap, prefix);
			DasCoordinateSystem[] dcss = getCoordinateSystemsForDasSource(conn,
					auto_id, coordmap);
			ds.setCoordinateSystem(dcss);

		}
		row.close();
		ps.close();

		return ds;
	}

	/**
	 * get a DAS source from the database based on the Id field
	 * 
	 * @param conn
	 * @param dassourceId
	 *            the unique ID of a DAS source
	 * @param prefix
	 *            the prefix of the registry installation
	 * @return the DasSource object
	 * @throws SQLException
	 */
	public DasSource getDasSourceById(Connection conn, String dassourceId,
			String prefix) throws SQLException {

		DasSource ds = new Das1Source();

		// String sql =
		// "SELECT auto_id,url,adminemail,description,registerDate," +
		// " leaseDate, helperurl,testcode,nickname,prefix,alert_admin "+
		// " from registry " ;

		String sql = BASIC_SELECT;

		boolean isLocal = hasLocalPrefix(dassourceId, prefix);

		if (isLocal)
			sql += " where r.auto_id = ?";
		else
			sql += " where r.prefix = ?";
		PreparedStatement ps = conn.prepareStatement(sql);

		if (isLocal) {
			String[] spl = dassourceId.split(DasRegistrySql.PREFIXSPLIT);

			if (spl.length != 2) {
				// logger.info("could not get auto id from Id " + dassourceId);
				return ds;
			}
			int db_auto_id = -1;
			try {
				db_auto_id = Integer.parseInt(spl[1]);
			} catch (Exception e) {
				e.printStackTrace();
				return ds;
			}

			// System.out.println("got auto_id " + db_auto_id + " for url " +
			// ds.getUrl());

			ps.setInt(1, db_auto_id);

		} else {
			ps.setString(1, dassourceId);
		}

		// if above if statement fails
		// that's very critical here, there really shouldbe an auto_id here :-/
		// if there is a problem, return empty das source ...

		DasCoordinateSystemCollector coordmap = getAllCoordinateSystemsMap(
				conn, prefix);

		ResultSet row = ps.executeQuery();

		if (row.next()) {
			ds = getDasSourceFromRow(conn, row, coordmap, prefix);
		}
		row.close();
		ps.close();

		return ds;
	}

	/**
	 * find a DAS source by its url
	 * 
	 * @param conn
	 * @param url
	 * @param prefix
	 * @return a new DAS source
	 * @throws SQLException
	 * */
	public DasSource getDasSourceByUrl(Connection conn, String url,
			String prefix) throws SQLException {

		DasSource ds = null;
		// String cmd = "SELECT auto_id,url,adminemail,description, "
		// +
		// " registerDate,leaseDate,helperurl,testcode,nickname,prefix,alert_admin "
		// + " from registry where url = ?";

		String cmd = BASIC_SELECT + " where url=?";
		System.out.println("cmd="+cmd);
		PreparedStatement ps = conn.prepareStatement(cmd);
		ps.setString(1, url);

		ResultSet row = ps.executeQuery();
		row.next();

		DasCoordinateSystemCollector coordmap = getAllCoordinateSystemsMap(
				conn, prefix);
		if (this.isInDatabase(conn, url)) {
			ds = getDasSourceFromRow(conn, row, coordmap, prefix);
		}
		row.close();
		ps.close();
		return ds;
	}

	public DasSource[] getDasSourcesByProjectID(Connection conn,
			int project_id, String prefix) throws SQLException {
		// could optimize this so it gets all sources with the project id at
		// once rather than
		// a query for each dassource returned afterwards.
		String sql = "select auto_id from registry where project_id = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, project_id);

		ResultSet row = ps.executeQuery();

		List<DasSource> sources = new ArrayList<DasSource>();
		DasCoordinateSystemCollector coordmap = getAllCoordinateSystemsMap(
				conn, prefix);

		while (row.next()) {
			int auto_id = row.getInt(1);
			DasSource ds = getDasSourceById(conn, auto_id, coordmap, prefix);
			sources.add(ds);

		}
		row.close();
		ps.close();

		return (DasSource[]) sources.toArray(new DasSource[sources.size()]);

	}

	public List<DasSource> getDasSourcesByOpenID(Connection conn,
			String openid, String prefix) throws SQLException {
		BeanFactory ctx = (new ClassPathXmlApplicationContext("SpringDAO.xml"));
		UserManager um = (UserManager) ctx.getBean("userManager");
		RegistryUser user = um.getUser(openid);
		if (user == null)
			return new ArrayList();
		int user_id = user.getUserID();

		String sql = "select auto_id from registry2user where user_id = ?";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, user_id);

		ResultSet row = ps.executeQuery();

		List<DasSource> sources = new ArrayList<DasSource>();
		DasCoordinateSystemCollector coordmap = getAllCoordinateSystemsMap(
				conn, prefix);

		while (row.next()) {
			int auto_id = row.getInt(1);
			DasSource ds = getDasSourceById(conn, auto_id, coordmap, prefix);
			sources.add(ds);

		}
		row.close();
		ps.close();

		return sources;

	}

	public List<Das2Mirror> getDasServersByOpenID(Connection conn, String openid)
			throws SQLException {
		UserManager um = new UserManager();

		RegistryUser user = um.getUser(openid);
		if (user == null)
			return new ArrayList();
		int user_id = user.getUserID();

		String sql = "select server_id, url from server where user_id = ?";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, user_id);

		ResultSet row = ps.executeQuery();

		List<Das2Mirror> servers = new ArrayList<Das2Mirror>();
		while (row.next()) {
			int server_id = row.getInt(1);
			String url = row.getString(2);
			Das2Mirror server = new Das2Mirror();
			server.setUrl(url);
			server.setServerId(server_id);
			servers.add(server);
		}
		return servers;
	}

	// /** set the owner of a DAS source
	// *
	// * @param conn
	// * @param url DAS source url
	// * @param openid
	// * @throws SQLException
	// */
	// public void setDasSourceOwner(Connection conn, String url, String openid)
	// throws SQLException {
	//
	// UserManager um = new UserManager();
	// RegistryUser user = um.getUser(openid);
	// int user_id = user.getUserID();
	//
	// //int auto_id = getAutoIdForURL(conn, url);
	// String sql = "update registry set user_id = ? where url = ?";
	// PreparedStatement ps = conn.prepareStatement(sql);
	//
	// ps.setInt(1,user_id);
	// ps.setString(2,url);
	// ps.executeUpdate();
	//
	// ps.close();
	//
	// }

	/**
	 * get the passkey for a server
	 * 
	 * @param conn
	 * @param url
	 * @return the unique key to remove/modify the DAS server
	 * @throws SQLException
	 * */
	public int getPasskey(Connection conn, String url) throws SQLException {
		String cmd = "SELECT passkey from registry where url = ?";
		// System.out.println(cmd + " " + url);
		PreparedStatement ps = conn.prepareStatement(cmd);
		ps.setString(1, url);
		ResultSet row = ps.executeQuery();
		// int passkey = -1;
		// while (row.next()) {
		row.next();
		int passkey = row.getInt(1);
		// }
		row.close();
		ps.close();
		return passkey;
	}

	// ** create a new coordinate system name */
	public int createNewCsName(Connection conn, String txt, String version)
			throws SQLException {
		if (txt.length() > 255) {
			throw new SQLException("name for coordinate system is too long!");
		}
		logger.fine("creating new cs name txt=" + txt + " version=" + version);
		String sql = "INSERT INTO cs_names (coordinateSystem, version ) values (?,?)";
		PreparedStatement ps = conn.prepareStatement(sql);

		ps.setString(1, txt);
		ps.setString(2, version);
		ps.executeUpdate();
		// get last insert id..
		int id = getLastInsertId(ps);
		ps.close();
		return id;
	}

	/**
	 * return the taxid for an organism
	 * 
	 * @param conn
	 * @param organism
	 * @return the NCBI taxonomy id
	 * @throws SQLException
	 */
	public int getNCBITaxId(Connection conn, String organism)
			throws SQLException {
		String sql = "select taxid from organism where preferred_name = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, organism);

		ResultSet rs = ps.executeQuery();
		int id = -1;
		if (rs.next()) {
			id = rs.getInt(1);
		}
		rs.close();
		ps.close();

		if (id != -1)
			return id;

		throw new SQLException("no organism >" + organism + "< found!");
	}

	public Integer[] getCategoryAutos(Connection conn, String[] categories)
			throws SQLException {

		List<Integer> lst = new ArrayList<Integer>();
		if (categories == null)
			return (Integer[]) lst.toArray(new Integer[lst.size()]);

		String sql = "select category_auto from category where category = ?";
		PreparedStatement ps = conn.prepareStatement(sql);

		for (int i = 0; i < categories.length; i++) {
			String category = categories[i];
			if (category == null)
				continue;
			if (category.equals("any"))
				continue;
			// System.out.println("getCategoryAutos >"+category+"<");
			ps.setString(1, category);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				lst.add(new Integer(rs.getInt(1)));
			}
			rs.close();

		}
		ps.close();

		return (Integer[]) lst.toArray(new Integer[lst.size()]);
	}

	public Integer[] getNameAutos(Connection conn, String[] names)
			throws SQLException {

		List<Integer> lst = new ArrayList<Integer>();
		if (names == null)
			return (Integer[]) lst.toArray(new Integer[lst.size()]);

		String sqlVersion = "select coord_auto from cs_names where coordinateSystem = ? and version = ?";
		PreparedStatement psVersion = conn.prepareStatement(sqlVersion);

		String sqlNoVersion = "select coord_auto from cs_names where coordinateSystem = ?";
		PreparedStatement psNoVersion = conn.prepareStatement(sqlNoVersion);

		for (int i = 0; i < names.length; i++) {
			String name = names[i];
			if (name == null)
				continue;
			if (name.equals("any"))
				continue;
			// System.out.println("getNameAutos >" + name+"<");
			String[] spl = name.split(DasRegistrySql.PREFIXSPLIT);

			ResultSet rs;
			if (spl.length == 2) {
				name = spl[0];
				String version = spl[1];
				psVersion.setString(1, name);
				psVersion.setString(2, version);
				rs = psVersion.executeQuery();
			} else {
				psNoVersion.setString(1, name);
				rs = psNoVersion.executeQuery();
			}

			while (rs.next()) {
				lst.add(new Integer(rs.getInt(1)));
			}

			rs.close();
		}
		psVersion.close();
		psNoVersion.close();

		return (Integer[]) lst.toArray(new Integer[lst.size()]);
	}

	public Integer[] getTaxIds(Connection conn, String[] organisms)
			throws SQLException {

		List<Integer> lst = new ArrayList<Integer>();
		if (organisms == null) {
			return (Integer[]) lst.toArray(new Integer[lst.size()]);
		}

		String sql = "select taxid from organism where preferred_name = ?";
		PreparedStatement ps = conn.prepareStatement(sql);

		for (int i = 0; i < organisms.length; i++) {
			String name = organisms[i];
			if (name == null)
				continue;
			if (name.equals("any"))
				continue;
			// System.out.println("getTaxIds >" + name+"<");
			ps.setString(1, name);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				lst.add(new Integer(rs.getInt(1)));
			}
			rs.close();
		}
		ps.close();
		return (Integer[]) lst.toArray(new Integer[lst.size()]);
	}

	public Integer[] getLabelAutos(Connection conn, String[] labels)
			throws SQLException {

		List<Integer> lst = new ArrayList<Integer>();
		if (labels == null)
			return (Integer[]) lst.toArray(new Integer[lst.size()]);

		String sql = "select label_auto from label where label = ?";
		PreparedStatement ps = conn.prepareStatement(sql);

		for (int i = 0; i < labels.length; i++) {
			String label = labels[i];
			if (label == null)
				continue;
			if (label.equals("any"))
				continue;
			if (label.equals(""))
				continue;
			// System.out.println("getLabelAutos >"+label+"<");
			ps.setString(1, label);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				lst.add(new Integer(rs.getInt(1)));
			}
			rs.close();
		}
		ps.close();
		return (Integer[]) lst.toArray(new Integer[lst.size()]);
	}

	/**
	 * create an organism and return the primary id of the row
	 * 
	 * @param conn
	 * @param name
	 * @param preferred_name
	 * @param taxid
	 * @return the auto-increment created by mysql
	 * @throws SQLException
	 */
	public int createOrganism(Connection conn, String name,
			String preferred_name, int taxid) throws SQLException {

		String sql = "INSERT INTO organism (name,preferred_name,taxid)  values (?,?,?)";
		PreparedStatement ps = conn.prepareStatement(sql);

		ps.setString(1, name);
		ps.setString(2, preferred_name);
		ps.setInt(3, taxid);
		ps.executeUpdate();
		// get last insert id..
		int id = getLastInsertId(ps);
		ps.close();
		return id;
	}

	public static int getLastInsertId(Statement stmt) throws SQLException {
		ResultSet rs = null;
		if (stmt != null) {
			rs = stmt.getGeneratedKeys();
		}

		int id = -1;
		if (rs != null && rs.next()) {
			id = rs.getInt(1);
		}

		rs.close();
		if (id != -1)
			return id;

		throw new SQLException("No LastInsertId available");

	}

	public int storeInDb(Connection conn, DasSource ds, String prefix) {
		return this.storeInDb(conn, ds, prefix, 0);
	}

	/**
	 * store a das source in the database
	 * 
	 * @param conn
	 * @param ds
	 * @param prefix
	 *            the prefix for comparing/ creating the coordinate systems
	 * @return the passkey for the newly registered das source
	 */
	public int storeInDb(Connection conn, DasSource ds, String prefix,
			int server_id) {

		String url = ds.getUrl();
		String nickname = ds.getNickname();
		String adminemail = ds.getAdminemail();
		String description = ds.getDescription();
		DasCoordinateSystem[] cs_types = ds.getCoordinateSystem();
		List<String> capabilities = ds.getCapabilities();

		String[] labels = ds.getLabels();
		String helperurl = ds.getHelperurl();

		logger.fine("attempting to store datasource in database url=" + url
				+ " nickname=" + nickname);
		logger.fine("adminmail=" + adminemail + " description=" + description
				+ " csTypes=" + cs_types.toString());
		System.out.println("capabilities=" + capabilities.toString());
		logger.fine("labels=" + labels.toString());
		logger.fine("helperurl=" + helperurl);

		int alert = 0;
		if (ds.getAlertAdmin())
			alert = 1;

		System.out.println("storeInDatabase labels.length:" + labels.length);

		int passkey = generatePasskey();
		try {
			String sql = "INSERT INTO registry " + "(url," + "nickname,"
					+ "adminemail," + "description," + "registerDate,"
					+ "leaseDate," + "passkey," + "helperurl," + "prefix,"
					+ "alert_admin," + "server_id, active";

			String endsql = ") values (?,?,?,?,now(),now(),?,?,?,?,?,?";
			
			List<Capabilities>capabilitiesObjects=Capabilities.capabilitiesListFromStrings(capabilities);
			for (int i = 0; i < capabilitiesObjects.size(); i++) {
				// potential security problem ?
				// no, because all capabilities are checked before
				// and unknown are not allowed
				// if dsn ignore
				if (!capabilitiesObjects.get(i).equals("dsn")) {
					sql += "," + capabilitiesObjects.get(i).getCommand();
					endsql += ",1";
				}
			}
			int auto_id = -9999;
			// if ( !ds.getId().equals(DasSource.EMPTY_ID)){
			// auto_id = getDbAutoId(ds.getId());
			// sql += ",auto_id";
			// endsql += ",?";
			// }
			sql += endsql + ")";

			char lastChar = url.charAt(url.length() - 1);
			if (lastChar != '/')
				url += "/";

			// System.out.println(sql);
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, url);
			ps.setString(2, nickname);
			ps.setString(3, adminemail);
			ps.setString(4, description);
			ps.setInt(5, passkey);
			ps.setString(6, helperurl);

			// prefix is depending if das source is local or not;

			// do the same for all sources now when first adding to database as
			// we don't want to treat mirrored sources any different
			ps.setString(7, null);

			ps.setInt(8, alert);
			ps.setInt(9, server_id);
			ps.setInt(10, 1);
			ps.executeUpdate();

			ResultSet rs = ps.getGeneratedKeys();

			if (rs.next()) {
				auto_id = rs.getInt(1);
			}
			// System.out.println("register new server with auto_id "+auto_id);
			rs.close();
			ps.close();

			for (int i = 0; i < cs_types.length; i++) {

				DasCoordinateSystem cs = cs_types[i];
				selectInsertCoordinateSystem(conn, cs, auto_id, prefix);
			}

			insertLabels(conn, auto_id, labels);
			logger
					.info("adding validated capabilities to db during registering of source");
			updateValidationStatusInDb(conn, auto_id, ds, prefix);
			updateSpecificationStatusInDb(conn, auto_id, ds.getSpecification());

			// System.out.println("cs inserted, Labels added");

		} catch (SQLException e) {
			e.printStackTrace();
			// System.out.println(e.getMessage());
		}

		return passkey;
	}

	public boolean updateInDb(Connection conn, int auto_id, DasSource ds,
			String prefix, int serverId) throws SQLException {

		System.out.println("updating das source " + auto_id + " "
				+ ds.getNickname());

		String url = ds.getUrl();
		String nickname = ds.getNickname();
		String adminemail = ds.getAdminemail();
		String description = ds.getDescription();
		DasCoordinateSystem[] cs_types = ds.getCoordinateSystem();
		List<String> capabilities = ds.getCapabilities();
		String spec=ds.getSpecification();
		String[] labels = ds.getLabels();
		String helperurl = ds.getHelperurl();
		// String testCode = ds.getTestCode();
		logger.fine("attempting to store datasource in database url=" + url
				+ " nickname=" + nickname);
		logger.fine("adminmail=" + adminemail + " description=" + description
				+ " csTypes=" + cs_types.toString());
		logger.fine("capabilities=" + capabilities.toString());
		logger.fine("labels=" + labels.toString());
		logger.fine("helperurl=" + helperurl);

		int alert = 0;
		if (ds.getAlertAdmin())
			alert = 1;

		// build up UPDATE statement ...
		String sql = "UPDATE registry set " + "url=?, " + "nickname=?, "
				+ "adminemail=?, " + "description=?, " + "leaseDate=now(), "
				+ "helperurl=?, " +

				"alert_admin=?," + "server_id=?, specification=?, active=1";

		// add the capabilities ..
		for (Capabilities cap : Capabilities.values()) {
			boolean found = false;
			String curr_cap = cap.toString();
			for (int i = 0; i < capabilities.size(); i++) {
				String mycap = capabilities.get(i);
				if (mycap.equals(curr_cap)) {
					found = true;
				}
			}
			sql += "," + curr_cap;
			if (found) {

				sql += "=1";
			} else {
				sql += "=NULL";
			}

		}

		sql += " where auto_id = ?";

		// System.out.println(sql);

		PreparedStatement ps = conn.prepareStatement(sql);

		char lastChar = url.charAt(url.length() - 1);
		if (lastChar != '/')
			url += "/";

		ps.setString(1, url);
		ps.setString(2, nickname);
		ps.setString(3, adminemail);
		ps.setString(4, description);
		ps.setString(5, helperurl);
		// ps.setString(6,testCode);
		ps.setInt(6, alert);
		ps.setInt(7, serverId);
		DasSpec dasSpec=DasSpec.convertToRegistrySpecification(spec);
		ps.setString(8, dasSpec.toString());
		ps.setInt(9, auto_id);
		// execute statement ...
		ps.executeUpdate();
		System.out.println(ps.toString());
		// remove capabilitites, labels
		removedCachedSecondaryData(conn, auto_id);

		for (int i = 0; i < cs_types.length; i++) {
			DasCoordinateSystem cs = cs_types[i];
			selectInsertCoordinateSystem(conn, cs, auto_id, prefix);
		}

		insertLabels(conn, auto_id, labels);
		ps.close();
		return true;

	}

	public boolean updateValidationStatusInDb(Connection conn, int auto_id,
			DasSource ds, String prefix) throws SQLException {

		logger.fine("updating validation status of das source " + auto_id
				+ " " + ds.getNickname());

		String url = ds.getUrl();
		String nickname = ds.getNickname();

		// need to check if the auto_id is already in the table of stored valid
		// sources
		// build up UPDATE statement ...
		String updateOrInsert = "UPDATE";
		if (!this.doesAutoIdExist(conn, auto_id)) {
			updateOrInsert = "INSERT";
		}
		String sql = updateOrInsert + " validated_capabilities set auto_id=? ";
		// add the capabilities ..

		List<String> validCapabilitiesStrings = ds.getValidCapabilities();
		List<Capabilities> validCapabilities=Capabilities.capabilitiesListFromStrings(validCapabilitiesStrings);
		boolean noValidCaps = false;
		if (validCapabilities == null) {
			// there are no valid capabilities for this server
			noValidCaps = true;
		}
		for (Capabilities cap : Capabilities.values()) {
			boolean found = false;
			String curr_cap = cap.toString();
			//System.out.println("curr_cap="+curr_cap);
			if (curr_cap.equalsIgnoreCase("dna"))
				continue;// ignore dna as not been valid for years!
			if (noValidCaps) {
				found = false;
			} else {
				for (int i = 0; i < validCapabilities.size(); i++) {

					Capabilities mycap = validCapabilities.get(i);
					//System.out.println(mycap.getCommand());
					if (mycap.getCommand().equals(curr_cap)) {
						found = true;
					}

				}
			}
			sql += "," + curr_cap;
			if (found) {

				sql += "=1";
			} else {
				sql += "=NULL";// TODO set this to 0 once testing of unstated
				// capabilities is implemented
			}

		}

		if (updateOrInsert.equalsIgnoreCase("update")) {
			sql += " where auto_id = ?";
		}

		logger.fine(sql);
		//System.out.println("validated capabilities sql="+sql);

		PreparedStatement ps = conn.prepareStatement(sql);

		ps.setInt(1, auto_id);

		if (updateOrInsert.equalsIgnoreCase("update")) {
			ps.setInt(2, auto_id);
		}
		// execute statement ...
		ps.executeUpdate();

		ps.close();

		return true;

	}

	public boolean updateTypesStatusInDb(Connection conn, int auto_id,
			DasSource ds, List<Map<String, String>> types) throws SQLException {

		logger.fine("updating types status of das source " + auto_id + " "
				+ ds.getNickname());
		if (types == null || types.size() < 1) {
			return false;
		}

		// need to check if the auto_id is already in the table of stored valid
		// sources
		// build up UPDATE statement ...

		// firstly we need to delete all types for this id then create the new
		// ones
		// delete from types where auto_id=136;
		String deleteSql = "delete from types where auto_id=" + auto_id;

		Statement s = conn.createStatement();
		boolean deleted = s.execute(deleteSql);
		s.close();

		// loop over all the types
		// insert into types (auto_id, id, cvId, category) values ('136',
		// 'gene', 'SO:1232', 'blah');
		// insert into types set auto_id='136' id='gene' cvId='SO:1232'
		// category='blah';
		String sql = null;
		Statement insertStatement = conn.createStatement();
		for (Map<String, String> type : types) {
			sql = "insert into types set auto_id='" + auto_id + "'";

			String id = type.get("id");
			String cvId = type.get("cvId");
			String category = type.get("category");
			String number = type.get("number");
			if (id != null) {
				sql += ", id='" + id + "'";
			}
			if (cvId != null) {
				sql += ", cvId='" + cvId + "'";
			}
			if (category != null) {
				sql += ", category='" + category + "'";
			}
			if (number != null) {
				sql += ", number='" + number + "'";
			}
			logger.fine(sql);

			insertStatement.execute(sql);

		}
		insertStatement.close();

		return true;

	}

	public boolean doesAutoIdExist(Connection conn, int auto_id) {
		try {
			PreparedStatement ps = conn
					.prepareStatement("SELECT COUNT(*) FROM validated_capabilities where auto_id = "
							+ auto_id);
			ResultSet row = ps.executeQuery();
			row.next();
			int size = row.getInt(1);
			ps.close();
			row.close();
			if (size > 0) {
				return true;
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * remove data related to the registry2xyz tables ...
	 * 
	 * @param conn
	 *            the database connection
	 * @param auto_id
	 *            the auto_id of the DasSource in the db- registry table
	 * @throws SQLException
	 */
	public void removedCachedSecondaryData(Connection conn, int auto_id)
			throws SQLException {

		String cmd2 = "delete from registry2coordNS where auto_id=?";
		doAutoIdSql(conn, cmd2, auto_id);

		String cmd3 = "delete from registry2label where auto_id=?";
		doAutoIdSql(conn, cmd3, auto_id);

		// also we now need to delete from the validated_capabilities table
		String cmd4 = "delete from validated_capabilities where auto_id=?";
		doAutoIdSql(conn, cmd4, auto_id);

		String cmd5 = "delete from types where auto_id=?";
		doAutoIdSql(conn, cmd5, auto_id);

		// TODO: registry2organism is obsolete
		// String cmd4 = "delete from registry2organism where auto_id=?";
		// doAutoIdSql(conn,cmd4,auto_id);

	}

	/**
	 * log the status of a DAS source in the uptime table - if all invalid no
	 * status at all for that date, if some valid but not all status =0 and has
	 * a current lease date in registry table, if all valid i.e. no red lights
	 * then status=1
	 * 
	 * @param conn
	 * @param url
	 * @param status
	 * @throws SQLException
	 */
	public void logStatus(Connection conn, String url, boolean status)
			throws SQLException {

		int auto_id = getAutoIdForURL(url);

		String sql = "INSERT INTO uptime ( auto_id, time, status) values (?,now(),?)";

		PreparedStatement ps = conn.prepareStatement(sql);

		ps.setInt(1, auto_id);
		if (status)
			ps.setInt(2, 1);
		else
			ps.setInt(2, 0);
		ps.executeUpdate();

		ps.close();

	}

	public DasCoordinateSystem getCoordinateSystem(Connection conn,
			int coordNS_auto) throws SQLException {
		DasCoordinateSystem coordSys = null;
		// System.out.println("XXXX in getCoordinAteSystem " + coordNS_auto);
		String sql = "select cs_names.coordinateSystem, category.category, ncbi_tax_id, "
				+ " coordNS_auto,coordNS.prefix, cs_names.version "
				+ " from coordNS, cs_names, category "
				+ " where coordNS.coord_auto = cs_names.coord_auto and "
				+ " coordNS.category_auto = category.category_auto "
				+ " and  coordNS.coordNS_auto = ?";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, coordNS_auto);

		String sqltax = " SELECT preferred_name from organism where taxid = ?";
		PreparedStatement tax = conn.prepareStatement(sqltax);

		ResultSet row = ps.executeQuery();
		while (row.next()) {
			// System.out.println(" XXXX got " + row.getString(1));
			String prefix = row.getString(5);
			if (prefix == null)
				prefix = "DS"; // TODO get this from config file
			coordSys = getCoordinateSystemFromRow(row, tax, prefix);
		}
		row.close();
		ps.close();

		return coordSys;
	}

	public DasCoordinateSystem getCoordinateSystem(Connection conn,
			int coord_auto, int category_auto, int tax_id) throws SQLException {
		DasCoordinateSystem coordSys = null;
		// System.out.println("XXXX in getCoordinAteSystem " + coordNS_auto);
		String sql = "select cs_names.coordinateSystem, category.category, ncbi_tax_id, "
				+ " coordNS_auto,coordNS.prefix, cs_names.version "
				+ " from coordNS, cs_names, category "
				+ " where coordNS.coord_auto = cs_names.coord_auto and "
				+ " coordNS.category_auto = category.category_auto "
				+ " and  coordNS.coord_auto = ?"
				+ " and  coordNS.category_auto = ?"
				+ " and  coordNS.ncbi_tax_id = ?";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, coord_auto);
		ps.setInt(2, category_auto);
		ps.setInt(3, tax_id);

		String sqltax = " SELECT preferred_name from organism where taxid = ?";
		PreparedStatement tax = conn.prepareStatement(sqltax);

		ResultSet row = ps.executeQuery();
		while (row.next()) {
			// System.out.println(" XXXX got " + row.getString(1));
			String prefix = row.getString(5);
			if (prefix == null)
				prefix = "DS"; // TODO get this from config file
			coordSys = getCoordinateSystemFromRow(row, tax, prefix);
		}
		row.close();
		ps.close();

		return coordSys;
	}

	private DasCoordinateSystem getCoordinateSystemFromRow(ResultSet row,
			PreparedStatement tax, String prefix) throws SQLException {

		String cs = row.getString(1);
		String ca = row.getString(2);
		int taxid = row.getInt(3);
		int coordNS_auto = row.getInt(4);
		String thisprefix = row.getString(5);
		String version = row.getString(6);

		String organism = "";
		if (taxid != 0) {
			tax.setInt(1, taxid);

			ResultSet taxres = tax.executeQuery();
			if (taxres.next()) {
				organism = taxres.getString(1);
			}
			taxres.close();
		}

		String csString = cs + "," + ca;
		if (!organism.equals("")) {
			csString += "," + organism;
		}
		DasCoordinateSystem dcs = new DasCoordinateSystem();
		dcs.setOrganismName(organism);
		dcs.setNCBITaxId(taxid);
		if (ca.equals(null))
			System.out.println("ca=" + ca);

		dcs.setCategory(ca);
		dcs.setName(cs);
		dcs.setVersion(version);
		if (thisprefix != null)
			dcs.setUniqueId(thisprefix);
		else {
			String uniqueCsName = "CS_" + prefix + coordNS_auto;
			dcs.setUniqueId(uniqueCsName);
		}
		return dcs;

	}

	public DasCoordinateSystemCollector getAllCoordinateSystemsMap(
			Connection conn, String prefix) throws SQLException {
		// System.out.println("DasRegistrySql: getAllCoordinateSystemsMap ");

		DasCoordinateSystemCollector csmap = new DasCoordinateSystemCollector();

		String sql = "select cs_names.coordinateSystem, category.category, ncbi_tax_id, "
				+ " coordNS_auto,coordNS.prefix, cs_names.version "
				+ " from coordNS, cs_names, category "
				+ " where coordNS.coord_auto = cs_names.coord_auto and "
				+ " coordNS.category_auto = category.category_auto "
				+ " order by coordNS.coord_auto";
		// System.out.println(sql);
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet row = ps.executeQuery();

		String sqltax = " SELECT preferred_name from organism where taxid = ?";
		PreparedStatement tax = conn.prepareStatement(sqltax);

		while (row.next()) {
			int coordNS_auto = row.getInt(4);
			DasCoordinateSystem dcs = getCoordinateSystemFromRow(row, tax,
					prefix);
			// System.out.println("new DAS coordsys " + dcs);
			// System.out.println(uniqueCsName);
			// add coordinate system to map
			csmap.addCoordinateSystem(coordNS_auto, dcs);

		}
		tax.close();
		row.close();
		ps.close();

		return csmap;
	}

	/**
	 * 
	 * @param conn
	 * @param coordsys
	 * @param auto_id
	 * @param prefix
	 * @throws SQLException
	 */
	public void selectInsertCoordinateSystem(Connection conn,
			DasCoordinateSystem coordsys, int auto_id, String prefix)
			throws SQLException {

		logger
				.fine("checking if coordinate system is in the registry for coordsys="
						+ coordsys
						+ " for auto_id= "
						+ auto_id
						+ " with prefix=" + prefix);
		// now we have modified coordinate systems
		// map the strings to the ids...
		DasCoordinateSystemCollector coordmap = getAllCoordinateSystemsMap(
				conn, prefix);

		// now map the coordsys string to the proper coordNS_auto value
		Set s = coordmap.keySet();
		Iterator iter = s.iterator();
		int coordNS_auto = -9999;
		logger.fine("checking " + coordsys);
		while (iter.hasNext()) {
			Integer key = (Integer) iter.next();
			DasCoordinateSystem csname = (DasCoordinateSystem) coordmap
					.get(key);
			// System.out.println(" comparing with " + csname);
			// System.out.println(csname.getUniqueId() + " " +
			// coordsys.getUniqueId());
			if (csname.getOrganismName().equalsIgnoreCase(
					coordsys.getOrganismName())) {
				logger.fine("organisms the same");
			}
			if (csname.equals(coordsys)) {
				// that's the one we are talking about
				coordNS_auto = key.intValue();
				logger.fine("intvalue found " + coordNS_auto);
				break;
			}
		}

		if (coordNS_auto == -9999) {
			throw new SQLException(
					"could not find coordinate system in database >" + coordsys
							+ "<");
		}

		// create new entry in registry2coordNS table;
		logger.info("creating a new entry in the registry2coordNS table");
		System.out.println("coords update= auto_id=" + auto_id
				+ " coordNS_auto=" + coordNS_auto + " testcode="
				+ coordsys.getTestCode());
		String sql = "INSERT INTO registry2coordNS ( auto_id, coordNS_auto,testregion ) values (?,?,?)";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, auto_id);
		ps.setInt(2, coordNS_auto);
		ps.setString(3, coordsys.getTestCode());
		ps.executeUpdate();
		ps.close();

	}

	public void insertLabels(Connection conn, int auto_id, String[] labels)
			throws SQLException {

		// store the labels...
		String labelregister = "INSERT INTO registry2label (label_auto, auto_id) values (?,?)";
		PreparedStatement psl = conn.prepareStatement(labelregister);
		String labelid = "SELECT label_auto from label where label = ?";
		PreparedStatement ps_label = conn.prepareStatement(labelid);
		for (int i = 0; i < labels.length; i++) {
			String label = labels[i];
			// System.out.println("testing for label " + label);
			// get label id;
			ps_label.setString(1, label);
			ResultSet lrow = ps_label.executeQuery();
			while (lrow.next()) {

				int label_auto = lrow.getInt(1);
				// System.out.println("registering label " + label + " " +
				// label_auto);
				psl.setInt(1, label_auto);
				psl.setInt(2, auto_id);
				psl.executeUpdate();
			}
			lrow.close();
		}
		psl.close();
		ps_label.close();

	}

	/* hm should this method be here? does not need any SQL ... */
	public static int generatePasskey() {
		java.util.Random generator = new java.util.Random();
		int m = generator.nextInt(9);
		int n = generator.nextInt(999);
		return n * 1000 + m;
	}

	public void releaseAdminLock(Connection conn, String actionName,
			String fieldLastName) throws SQLException {
		String sql = "update admin set " + actionName + " = 0, "
				+ fieldLastName + " = now() where adid = 1 ";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.executeUpdate();
		ps.close();

	}

	private boolean grabAdminAction(Connection conn, int adid,
			String actionName, String fieldStartedName) throws SQLException {

		boolean status = true;

		System.out.println("grabbing " + actionName);
		String sql = "";
		if (adid < 0) {
			sql = "insert into admin (adid, " + actionName + ", "
					+ fieldStartedName + " ) values (1,?,now())";
		} else {
			sql = "update admin set " + actionName + "  = ?, "
					+ fieldStartedName + " = now()  where adid = ?";
		}

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, 1);

		if (adid > 0) {
			ps.setInt(2, adid);
		}

		ps.executeUpdate();
		ps.close();

		return status;
	}

	/**
	 * if this servlet is run on a cluster only one of the machines should do
	 * the acutal administrative task like mirroring or validation.
	 * 
	 * @param conn
	 * @param fieldLastName
	 * @param fieldStartedName
	 * @param actionName
	 * @return flag if the action should be done now
	 * @throws SQLException
	 */
	public boolean doAdminChecks(Connection conn, String fieldLastName,
			String fieldStartedName, String actionName) throws SQLException {

		// LOCK ADMIN TABLE
		String lock = "LOCK TABLES admin WRITE;";
		PreparedStatement psLock = conn.prepareStatement(lock);
		psLock.execute();
		psLock.close();

		String sql = "SELECT adid, " + fieldLastName + ", " + fieldStartedName
				+ " , " + actionName + "  from admin";

		PreparedStatement ps = conn.prepareStatement(sql);

		ResultSet rs = ps.executeQuery();

		boolean grabAction = true;
		int adid = -1;
		// if nothing in database, then do action.

		while (rs.next()) {
			// also there is only one row in this table

			grabAction = false;

			adid = rs.getInt(1);
			Timestamp lastAction = rs.getTimestamp(2);
			Timestamp actionStarted = rs.getTimestamp(3);
			int validating = rs.getInt(4);

			// System.out.println(adid + " " + actionName + " last:" +
			// lastAction
			// + " started:" + actionStarted + " currently running:"
			// + validating);

			if (actionStarted == null) {
				// first time
				grabAction = true;
				continue;
			}

			java.util.Date now = new java.util.Date();
			long lastActionTime = actionStarted.getTime();
			long nowTime = now.getTime();
			long lastdiff = nowTime - lastActionTime;

			long lastValidationStart = actionStarted.getTime();
			long startDiff = nowTime - lastValidationStart;
			// System.out.println("startDiff="+startDiff);

			boolean actionRunning = false;

			// another process is already running ...
			if ((validating == 1) && (startDiff < THREE_HOURS)) {
				actionRunning = true;
				grabAction = false;

			}

			if ((validating == 0) && (lastdiff > THREE_HOURS)) {
				if (!actionRunning)
					grabAction = true;
			}
			if (!actionRunning) {
				grabAction = true;
			}

			// only do this every 1 hours ...
			// overrules the other checks ...
			if (lastdiff < THREE_HOURS) {
				// grabAction = false;

				grabAction = false;
				System.out.println("fineging? no, grabaction set to "
						+ grabAction);
			}

		}

		rs.close();
		ps.close();

		boolean doAction = false;

		if (grabAction) {
			doAction = grabAdminAction(conn, adid, actionName, fieldStartedName);

		} else {
			doAction = false;
		}

		// UNLOCK ADMIN TABLE
		String unlock = "UNLOCK TABLES ;";
		PreparedStatement psUnlock = conn.prepareStatement(unlock);
		psUnlock.execute();
		psUnlock.close();

		return doAction;

	}

	/**
	 * select all types of coordinate systems from the database
	 * 
	 * @param conn
	 * @return the different types of coords, etc Protein Sequence, Chromosome
	 * @throws SQLException
	 */
	public String[] getCoordSysTypes(Connection conn) throws SQLException {

		String SELECTCOORDSYSTYPES = "SELECT category from category";

		List<String> lst = new ArrayList<String>();

		PreparedStatement selectCoordSyTypes = conn
				.prepareStatement(SELECTCOORDSYSTYPES);
		ResultSet rs = selectCoordSyTypes.executeQuery();

		while (rs.next()) {
			String category = rs.getString(1);
			lst.add(category);
		}
		rs.close();
		selectCoordSyTypes.close();
		return (String[]) lst.toArray(new String[lst.size()]);
	}

	/**
	 * get the name of all organisms
	 * 
	 * @param conn
	 * @return the names of all organisms in the database
	 * @throws SQLException
	 */
	public String[] getOrganismsByName(Connection conn) throws SQLException {

		String SELECTORGANISMS = " SELECT preferred_name  from organism order by organism_auto";

		List<String> lst = new ArrayList<String>();

		PreparedStatement selectorgs = conn.prepareStatement(SELECTORGANISMS);
		ResultSet rs = selectorgs.executeQuery();
		while (rs.next()) {
			String organism = rs.getString(1);
			lst.add(organism);
		}
		rs.close();
		selectorgs.close();
		return (String[]) lst.toArray(new String[lst.size()]);
	}

	/**
	 * get the name of all organisms
	 * 
	 * @param conn
	 * @return the names of all organisms in the database
	 * @throws SQLException
	 */
	public String[] getOrganismsByName(String startOfName, Connection conn)
			throws SQLException {

		String SELECTORGANISMS = " SELECT preferred_name  from organism where preferred_name like '"
				+ startOfName + "%' order by preferred_name";

		List<String> lst = new ArrayList<String>();

		PreparedStatement selectorgs = conn.prepareStatement(SELECTORGANISMS);
		ResultSet rs = selectorgs.executeQuery();
		while (rs.next()) {
			String organism = rs.getString(1);
			lst.add(organism);
		}
		rs.close();
		selectorgs.close();
		return (String[]) lst.toArray(new String[lst.size()]);
	}

	/**
	 * get the name of an organism identified by its NCBI taxonomy ID
	 * 
	 * @param conn
	 * @param ncbiId
	 * @return the name of the organism according to the database, or null if
	 *         not found
	 * @throws SQLException
	 */
	public String getOrganismByNCBITaxId(Connection conn, int ncbiId)
			throws SQLException {

		String SELECTORGANISM = " SELECT preferred_name  from organism where taxid = ?";

		String organism = null;

		PreparedStatement selectorgs = conn.prepareStatement(SELECTORGANISM);
		selectorgs.setInt(1, ncbiId);
		ResultSet rs = selectorgs.executeQuery();
		if (rs.next()) {
			organism = rs.getString(1);
		}
		rs.close();
		selectorgs.close();
		return organism;
	}

	/**
	 * load all data from the organism table
	 * 
	 * @param conn
	 * @return a Map where each entry corresponds to one organism and the index
	 *         is the primary key from the database
	 * 
	 * @throws SQLException
	 */
	public OrganismBean[] getAllOrganism(Connection conn) throws SQLException {

		String sql = "select organism_auto, name, preferred_name, taxid from organism";

		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		List<OrganismBean> orgs = new ArrayList<OrganismBean>();
		while (rs.next()) {
			int key = rs.getInt(1);
			String name = rs.getString(2);
			String preferred_name = rs.getString(3);
			int taxid = rs.getInt(4);
			OrganismBean org = new OrganismBean();
			org.setPrimaryKey(key);
			org.setName(name);
			org.setScientificName(preferred_name);
			org.setTaxonomyId(taxid);
			orgs.add(org);

		}
		rs.close();
		ps.close();

		return (OrganismBean[]) orgs.toArray(new OrganismBean[orgs.size()]);
	}

	/**
	 * return all coordinate system "authorities" which are of a certain type
	 * type is e.g. Chromosome, Protein Sequence, etc.
	 * 
	 * 
	 * 
	 * @param conn
	 * @param type
	 *            e.g. Chromosome, Protein Sequence
	 * @return the coordinateSystem authority e.g. UniProt
	 * @throws SQLException
	 */

	public String[] getCoordSysByType(Connection conn, String type)
			throws SQLException {
		List<String> lst = new ArrayList<String>();

		String SELECTCOORDSYSBYTYPE = "SELECT coordinateSystem "
				+ " from cs_names, category "
				+ " where category.category = ? and "
				+ " category.category_auto = cs_names.category_auto";

		PreparedStatement selectCoordSysByType = conn
				.prepareStatement(SELECTCOORDSYSBYTYPE);
		selectCoordSysByType.setString(1, type);
		ResultSet rs = selectCoordSysByType.executeQuery();

		while (rs.next()) {
			String coordsys = rs.getString(1);
			lst.add(coordsys);
		}
		rs.close();
		selectCoordSysByType.close();
		return (String[]) lst.toArray(new String[lst.size()]);

	}

	/**
	 * returns the number of available sources in registry
	 * 
	 * @param conn
	 * @return the total number of sources
	 * 
	 */

	public int getNumberSources(Connection conn) {
		try {
			PreparedStatement ps = conn
					.prepareStatement("SELECT COUNT(*) FROM registry where protocol != 2");
			ResultSet row = ps.executeQuery();
			row.next();
			int size = row.getInt(1);
			ps.close();
			row.close();
			return size;

		} catch (Exception e) {
			e.printStackTrace();
		}
		return 0;
	}

	// part for auto-notification

	public String[] getUnhappyServerAdmins(Connection conn) throws SQLException {
		int intervalBetweenNotValidAndEmailSentInDays = 2;
		String sqladmin = "SELECT distinct(adminemail) "
				+ " from registry where DATE_ADD(leaseDate, INTERVAL "
				+ intervalBetweenNotValidAndEmailSentInDays + " day ) < now()"
				+ " and alert_admin = 1 and alert_sent = 0 and prefix IS NULL;";

		PreparedStatement psadmin = conn.prepareStatement(sqladmin);

		ResultSet row = psadmin.executeQuery();

		List<String> emails = new ArrayList<String>();
		while (row.next()) {
			String email = row.getString(1);
			emails.add(email);
		}

		row.close();
		psadmin.close();

		String sql = "select distinct(u.email) from user u, registry r, registry2user r2u "
				+ " where DATE_ADD(leaseDate, INTERVAL 2 day ) < now()"
				+ " and alert_sent = 0 and prefix IS NULL"
				+ " and r.auto_id = r2u.auto_id and u.user_id = r2u.user_id "
				+ " and u.alert_me = 1";

		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet set = ps.executeQuery();
		while (set.next()) {
			String email = set.getString(1);
			if (!emails.contains(email))
				emails.add(email);
		}
		set.close();
		ps.close();

		return (String[]) emails.toArray(new String[emails.size()]);

	}

	public String[] getLastWeekWarningServerAdmins(Connection conn)
			throws SQLException {
		int intervalBetweenNotValidAndEmailSentInDays = 53;// 53 as 7 days
		// before deletion
		// if archive time
		// is 60 days
		String sqladmin = "SELECT distinct(adminemail) "
				+ " from registry where DATE_ADD(leaseDate, INTERVAL "
				+ intervalBetweenNotValidAndEmailSentInDays + " day ) < now()"
				+ " and alert_admin = 1 and alert_sent = 1 and prefix IS NULL;";

		PreparedStatement psadmin = conn.prepareStatement(sqladmin);

		ResultSet row = psadmin.executeQuery();

		List<String> emails = new ArrayList<String>();
		while (row.next()) {
			String email = row.getString(1);
			emails.add(email);
		}

		row.close();
		psadmin.close();

		String sql = "select distinct(u.email) from user u, registry r, registry2user r2u "
				+ " where DATE_ADD(leaseDate, INTERVAL "
				+ intervalBetweenNotValidAndEmailSentInDays
				+ "day ) < now()"
				+ " and alert_sent = 1 and prefix IS NULL"
				+ " and r.auto_id = r2u.auto_id and u.user_id = r2u.user_id "
				+ " and u.alert_me = 1";

		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet set = ps.executeQuery();
		while (set.next()) {
			String email = set.getString(1);
			if (!emails.contains(email))
				emails.add(email);
		}
		set.close();
		ps.close();

		return (String[]) emails.toArray(new String[emails.size()]);

	}

	public DasSource[] getUnhappyDasSourcesForAdmin(Connection conn,
			String adminemail, DasCoordinateSystemCollector coordmap,
			String prefix, int days, int alertSent) throws SQLException {

		List<DasSource> sources = new ArrayList<DasSource>();

		String selectcmd = "SELECT auto_id,url,adminemail,description,registerDate,"
				+ " leaseDate, helperurl,testcode,nickname,prefix, alert_admin, specification "
				+ " from registry where adminemail = ? and DATE_ADD(leaseDate, INTERVAL "
				+ days
				+ " day) < now()"
				+ " and alert_sent = "
				+ alertSent
				+ " and prefix IS NULL; ";

		PreparedStatement psselect = conn.prepareStatement(selectcmd);

		psselect.setString(1, adminemail);
		ResultSet selected = psselect.executeQuery();

		while (selected.next()) {

			DasSource ds = getDasSourceFromRow(conn, selected, coordmap, prefix);
			sources.add(ds);
		}
		selected.close();
		psselect.close();

		// now check those users which want to be reminded of all their DAS
		// sources
		// that are down

		String sql = "SELECT r.auto_id,r.url "
				+ " from registry r, registry2user r2u, user u "
				+ " where u.email= ? and u.alert_me=1 and r2u.user_id = u.user_id and r2u.auto_id = r.auto_id "
				+ " and DATE_ADD(leaseDate, INTERVAL " + days + " day) < now()"
				+ " and alert_sent = " + alertSent + " and prefix IS NULL; ";

		PreparedStatement ps = conn.prepareStatement(sql);

		ps.setString(1, adminemail);
		ResultSet set = ps.executeQuery();
		while (set.next()) {

			// DasSource ds = getDasSourceFromRow(conn,set,coordmap,prefix);
			String url = set.getString(2);

			DasSource ds = getDasSourceByUrl(conn, url, prefix);

			boolean found = false;
			Iterator iter = sources.iterator();
			while (iter.hasNext()) {
				DasSource kds = (DasSource) iter.next();
				if (kds.equals(ds)) {
					found = true;
					break;
				}
			}
			if (!found)
				sources.add(ds);

		}
		set.close();
		ps.close();

		return (DasSource[]) sources.toArray(new DasSource[sources.size()]);

	}

	/**
	 * an email has been sent to the administrator of unhappy DAS sources set
	 * the flag for this in the database
	 * 
	 * @param conn
	 * @param url
	 * @param status
	 *            TODO
	 * @throws SQLException
	 */
	public void unhappyAlertSent(Connection conn, String url, int status)
			throws SQLException {

		String updatecmd = "UPDATE registry set alert_sent=" + status
				+ " where url=? ";

		PreparedStatement updatesql = conn.prepareStatement(updatecmd);
		// now set the flag to sent

		updatesql.setString(1, url);
		updatesql.executeUpdate();

		updatesql.close();

	}

	public Timestamp getReminderDate(Connection conn, String url)
			throws SQLException {
		String cmd = "select reminderDate from registry where url = ?";
		PreparedStatement ps = conn.prepareStatement(cmd);
		ps.setString(1, url);

		ResultSet row = ps.executeQuery();
		java.util.Date now = new java.util.Date();
		Timestamp d = new Timestamp(now.getTime());
		while (row.next()) {
			d = row.getTimestamp(1);
		}
		row.close();
		ps.close();
		return d;
	}

	public void setReminderDate(Connection conn, String url)
			throws SQLException {
		String cmd = "update registry set reminderDate = now() where url = ?";
		PreparedStatement ps = conn.prepareStatement(cmd);
		ps.setString(1, url);
		ps.executeUpdate();
		ps.close();

	}

	public Map getTrafficLightData(Connection conn, int auto_id)
			throws SQLException {
		Map<String, String> traffic = new HashMap<String, String>();

		String sql = "select feature,version,types,entry_point,dsn "
				+ "from biosapiensTest where auto_id = ?";

		PreparedStatement ps = conn.prepareStatement(sql);

		ps.setInt(1, auto_id);

		ResultSet set = ps.executeQuery();

		while (set.next()) {
			traffic.put("feature", set.getInt(1) + "");
			traffic.put("version", set.getInt(2) + "");
			traffic.put("types", set.getInt(3) + "");
			traffic.put("entry_points", set.getInt(4) + "");
			traffic.put("dsn", set.getInt(5) + "");
		}

		set.close();
		ps.close();

		return traffic;
	}

	public DasCoordinateSystemCollector getCoordinatesByName(
			String startOfOrganismName, String prefix, Connection conn)
			throws SQLException {
		DasCoordinateSystemCollector csmap = new DasCoordinateSystemCollector();
		// like '"+startOfName+"%'
		// select cs_names.coordinateSystem, category.category, ncbi_tax_id,
		// coordNS_auto,coordNS.prefix, cs_names.version,
		// organism.preferred_name from coordNS, cs_names, category, organism
		// where coordNS.coord_auto = cs_names.coord_auto and
		// coordNS.category_auto = category.category_auto and
		// organism.taxid=ncbi_tax_id order by organism.preferred_name;

		// select cs_names.coordinateSystem, category.category, ncbi_tax_id,
		// coordNS_auto,coordNS.prefix, cs_names.version,
		// organism.preferred_name from coordNS, cs_names, category, organism
		// where coordNS.coord_auto = cs_names.coord_auto and
		// coordNS.category_auto = category.category_auto and
		// organism.taxid=ncbi_tax_id and organism.preferred_name like 'homo%'
		// order by organism.preferred_name;

		String sql = "select cs_names.coordinateSystem, category.category, ncbi_tax_id, "
				+ " coordNS_auto,coordNS.prefix, cs_names.version , organism.preferred_name"
				+ " from coordNS, cs_names, category, organism"
				+ " where coordNS.coord_auto = cs_names.coord_auto and "
				+ " coordNS.category_auto = category.category_auto "
				+ " and organism.taxid=ncbi_tax_id and organism.preferred_name like '"
				+ startOfOrganismName + "%'   order by organism.preferred_name";
		// System.out.println(sql);
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet row = ps.executeQuery();

		String sqltax = " SELECT preferred_name from organism where taxid = ?";
		PreparedStatement tax = conn.prepareStatement(sqltax);

		while (row.next()) {
			int coordNS_auto = row.getInt(4);
			DasCoordinateSystem dcs = getCoordinateSystemFromRow(row, tax,
					prefix);
			// System.out.println("new DAS coordsys " + dcs);
			// System.out.println(uniqueCsName);
			// add coordinate system to map
			csmap.addCoordinateSystem(coordNS_auto, dcs);

		}
		tax.close();
		row.close();
		ps.close();

		return csmap;
	}

	private List<String> getValidCapabilities(Connection conn, int auto_id) {

		// String []validCaps={"features","source","stylesheet"};

		// logger.fine("getValidCapabilities");
		List<String> validCapabilities = new ArrayList<String>();
		try {
			

			String sql = sqlUtil.getValidCapabilitiesSql();
			//System.out.println(sql);
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, auto_id);

			ResultSet row = ps.executeQuery();

			// only one result expected ...
			boolean ok = row.next();
			if (!ok) {
				logger
						.fine("did not find a result set for auto_id "
								+ auto_id);
			} else {

				/* test which capabilities are supported by this service */
				for (Capabilities capab : EnumSet.allOf(Capabilities.class)) {
					String cap = capab.getCommand();
					if (!cap.equalsIgnoreCase("dna")) {// ignoring dna as too
						// old
						if (hasCapability(row, cap)) {
							validCapabilities.add(capab.getName());
							// System.out.println("found capability:" +cap);
							// all_capabilities.get(i));
						}
					}
				}
				// System.out.println("cap size"+capabilities.size());
				
				return validCapabilities;
			}

			row.close();
			ps.close();
			// System.out.println(str.length);
			return validCapabilities;

		} catch (Exception e) {
			e.printStackTrace();
			logger.info("not got any valid capabilities for a source");
		}
		logger.info("not got any valid capabilities for a source");
	return validCapabilities;

	}

	public List<Map<String, String>> getTypesFromDb(Connection conn, int auto_id) {
		List<Map<String, String>> types = new ArrayList<Map<String, String>>();
		try {

			String sql = "select * from types where auto_id=" + auto_id;
			PreparedStatement ps = conn.prepareStatement(sql);

			// System.out.println(sql);
			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				Map<String, String> map = new HashMap<String, String>();
				String id = rs.getString("id");
				String cvId = rs.getString("cvId");
				String category = rs.getString("category");
				int number = rs.getInt("number");

				map.put("id", id);
				map.put("category", category);
				map.put("cvId", cvId);
				map.put("number", Integer.toString(number));
				types.add(map);
			}
			rs.close();
			ps.close();
			// System.out.println(str.length);

		} catch (Exception e) {
			e.printStackTrace();
			logger.info("not got any types for a source");
		}

		return types;
	}

	public boolean updateSpecificationStatusInDb(Connection conn, int auto_id,
			String spec) {
		// update registry set specification=1.5 where auto_id=109;
		String sql = "update registry set specification=? where auto_id=?";
		try {
			PreparedStatement prep = conn.prepareStatement(sql);
			prep.setString(1, spec);
			prep.setInt(2, auto_id);
			prep.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return true;
	}

	public String[] getUnhappyDeletingSourceServerAdmins(Connection conn)
			throws SQLException {

		// send email if alert sent=1 as the infoing has already been given that
		// deletion will occur.
		String sqladmin = "SELECT distinct(adminemail) "
				+ " from registry where DATE_ADD(leaseDate, INTERVAL "
				+ DasRegistrySql.ARCHIVE_TIME + " day ) < now()"
				+ " and prefix IS NULL;";
		// we send email whether alert me is set on not for deleting of sources
		// and we can ignore alert sent as it's going to be going anyway
		PreparedStatement psadmin = conn.prepareStatement(sqladmin);

		ResultSet row = psadmin.executeQuery();

		List<String> emails = new ArrayList<String>();
		while (row.next()) {
			String email = row.getString(1);
			emails.add(email);
		}

		row.close();
		psadmin.close();

		String sql = "select distinct(u.email) from user u, registry r, registry2user r2u "
				+ " where DATE_ADD(leaseDate, INTERVAL "
				+ DasRegistrySql.ARCHIVE_TIME
				+ " day ) < now()"
				+ " and prefix IS NULL"
				+ " and r.auto_id = r2u.auto_id and u.user_id = r2u.user_id ";

		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet set = ps.executeQuery();
		while (set.next()) {
			String email = set.getString(1);
			if (!emails.contains(email))
				emails.add(email);
		}
		set.close();
		ps.close();

		return (String[]) emails.toArray(new String[emails.size()]);

	}

	public List<DasSource> getSourcesforArchiving(Connection conn, String prefix)
			throws SQLException {

		DasCoordinateSystemCollector coordmap = getAllCoordinateSystemsMap(
				conn, prefix);

		String cmd2 = BASIC_SELECT
				+ " where protocol != 2 and DATE_ADD(leaseDate, INTERVAL "
				+ DasRegistrySql.ARCHIVE_TIME + " day ) < now() ";
		// String cmd2 = sqlUtil.getAllSourcesSql();
		PreparedStatement ps = conn.prepareStatement(cmd2);
		logger.fine("sql for all sources: " + cmd2);
		System.out.println(cmd2);
		ResultSet row = ps.executeQuery();

		List<DasSource> sources = new ArrayList<DasSource>();
		// int i = 0;
		while (row.next()) {
			// i ++;
			// DasSource ds = getDasSourceFromRow(conn, row, coordmap, prefix);
			DasSource ds = getDasSourceFromRow(conn, row, coordmap, prefix);
			// System.out.println(ds);
			// System.out.println("setting " + i + "adding: " + knowSourcesSize
			// + ds.getUrl());
			// System.out.println("i="+i);
			sources.add(ds);
		}

		// System.out.println("returning"+sources[0] );
		row.close();
		ps.close();

		return sources;

	}

	/**
	 * change status of source to inactive in database
	 * 
	 * @param serverUrl
	 * @return
	 */
	public String archiveSource(String sourceUrl) {
		int auto_id = sourceDAO.getAutoIdForURL(sourceUrl);

		return sourceDAO.setActiveSource(auto_id, false);
	}

	public String restoreSource(String sourceUrl) {
		int auto_id = sourceDAO.getAutoIdForURL(sourceUrl);
		return sourceDAO.setActiveSource(auto_id, true);
	}

	public List<DasSource> getAllArchvedSources(Connection conn, String prefix)
			throws SQLException {

		String cmd2 = "SELECT "
				+ " distinct(r.auto_id),r.url,r.adminemail,r.description,r.registerDate,"
				+ " r.leaseDate, r.helperurl, r.testcode, r.nickname, r.prefix, r.alert_admin, r.specification "
				+ " from registryarchive r";
		;
		// String cmd2 = sqlUtil.getAllSourcesSql();
		PreparedStatement ps = conn.prepareStatement(cmd2);
		// System.out.println(cmd2);
		ResultSet row = ps.executeQuery();

		List<DasSource> archivedSources = new ArrayList<DasSource>();
		// int i = 0;
		while (row.next()) {
			// i ++;
			// DasSource ds = getDasSourceFromRow(conn, row, coordmap, prefix);
			DasSource ds = getArchivedDasSourceFromRow(conn, row, prefix);

			archivedSources.add(ds);
		}

		// System.out.println("returning"+sources[0] );
		row.close();
		ps.close();
		return archivedSources;
	}

	private DasSource getArchivedDasSourceFromRow(Connection conn,
			ResultSet row, String prefix) throws SQLException {
		int auto_id = row.getInt(1);
		String url = row.getString(2);
		String adminemail = row.getString(3);
		String description = row.getString(4);
		Timestamp regTime = row.getTimestamp(5);
		Timestamp leaseTime = row.getTimestamp(6);
		String helperurl = row.getString(7);
		String nickname = row.getString(9);
		String thisprefix = row.getString(10);
		String alertMe = row.getString(11);
		String specification = row.getString("specification");

		// System.out.println("DasSourceManager: converting the dates");
		java.util.Date registerDate = new Date(regTime.getTime());
		java.util.Date leaseDate = new Date(leaseTime.getTime());

		DasSource ds = new Das1Source();

		String idstring = prefix + DasRegistrySql.PREFIXSPLIT + auto_id;
		// System.out.println(" in getSourceFromRow="+idstring);
		// if (thisprefix == null) {
		// ds.setLocal(true);
		// } else {
		// ds.setLocal(false);
		// idstring = thisprefix;
		// }

		ds.setId(idstring);
		ds.setUrl(url);
		ds.setAdminemail(adminemail);
		ds.setDescription(description);
		ds.setRegisterDate(registerDate);
		ds.setLeaseDate(leaseDate);
		ds.setHelperurl(helperurl);
		if (specification != null) {
			ds.setSpecification(specification);
		} else {
			ds.setSpecification("Not specified by server");
		}
		return ds;
	}

	public boolean updateInDb(Connection conn, int autoId, DasSource ds,
			String prefix) throws SQLException {
		return this.updateInDb(conn, autoId, ds, prefix, 0);
	}

	public boolean addServerIdToSource(Connection conn, String sourceUrl,
			int serverId) {
		// update registry set specification=1.5 where auto_id=109;
		String sql = "update registry set server_id=? where url=?";
		try {
			// System.out.println(sql+sourceUrl+" "+serverId);
			PreparedStatement prep = conn.prepareStatement(sql);
			prep.setInt(1, serverId);
			prep.setString(2, sourceUrl);
			prep.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return true;

	}

	public void addServerIdToSources(List<DasSource> sources, int serverId) {

		for (DasSource source : sources) {
			String errors = sourceDAO.addServerIdToSource(source.getUrl(),
					serverId);
			if (!errors.equals("")) {
				logger.info(errors);
			}
		}

	}

	public List<DasSource> getInactiveSources() {
		return sourceDAO.getInactiveSources();
	}

	public boolean updateTimesStatusInDb(int autoId, DasSource ds,
			Map<Capabilities, Long> times) {

		String errors = sourceDAO.setTimesForDataSource(times, autoId);
		if (errors.length() < 0) {
			return true;
		}
		return false;

	}
	
	public Map<Capabilities, Long> getCapabilityTimes(int auto_id){
		return sourceDAO.getCapabilityTimes(auto_id);
	}

	/**
	 * convenience method for JUnit testing - not to be used in registry as we use removeLocal source to set inactive instead
	 * @param auto_id
	 */
	public int deletDasSource(int auto_id) {
		int rowsAffected=sourceDAO.deleteSource(auto_id);
		return rowsAffected;
		
	}

}
